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

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”.

Step

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

Step

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

Step

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

Step

Type the following to create the “CityTbl” table:

Step

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

Step

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

Step

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

Step

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

Step

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

Step

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

Step

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

Step

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

Step

Press “F5” to execute the subroutine.