How to Access VBA Query Results

Knowing how to read the results from a Microsoft Access query can help you make your Access application more dynamic. Access is part of the Microsoft Office suite and it's used to create relational databases. Visual Basic for Applications (VBA) can be used in Access to create Windows applications and retrieve database data. A query is built using Structured Query Language (SQL) statements to request information from a database.

Retrieve query results in Access using VBA.

Step 1

Start Microsoft Office Access 2007 and select "Blank Database," then click "Create." Click "Database Tools" and select "Visual Basic." Click the "Insert" field and select "Module".

Type "Private sub readQueryResults" and press "Enter" to create a new subroutine.

Step 2

Type the following to declare variables you will use to read the query results:

Dim RecordSt As Recordset Dim dBase As Database Dim stringSQL As String Dim rCnt As Integer

Step 3

Type the following to create the "CityTbl" table:

stringSQL = "CREATE TABLE CityTbl (City TEXT(25), State TEXT(25));" DoCmd.RunSQL (stringSQL)

Step 4

Type the following to add two new records to your "CityTbl" table:

stringSQL = "INSERT INTO CityTbl ([City], [State] ) " stringSQL = stringSQL & "VALUES ('Fort Worth', 'Texas');" DoCmd.SetWarnings False DoCmd.RunSQL (stringSQL)

stringSQL = "INSERT INTO CityTbl ([City], [State] ) " stringSQL = stringSQL & "VALUES ('Dallas', 'Texas');" DoCmd.SetWarnings False DoCmd.RunSQL (stringSQL)

Step 5

Type the following to define your query and open your Recordset:

stringSQL = "SELECT CityTbl.* FROM CityTbl;" Set RecordSt = dBase.OpenRecordset(stringSQL)

Step 6

Type the following to create a "For Loop" and display your query results through a message box:

RecordSt.MoveFirst For rCnt = 0 To RecordSt.RecordCount MsgBox (RecordSt.Fields("City").Value & ", " & RecordSt.Fields("State").Value) RecordSt.MoveNext Next rCnt

Press "F5" to execute the subroutine.

references