How to Access VBA Query Results

Techwalla may earn compensation through affiliate links in this story.
Retrieve query results in Access using VBA.

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.

Advertisement

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

Video of the Day

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:

Advertisement

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:

Advertisement

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:

Advertisement

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.

Advertisement

references