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.
Things You'll Need
- Microsoft Office Access 2007
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.
Type the following to declare variables you will use to read the query results:Dim RecordSt As RecordsetDim dBase As DatabaseDim stringSQL As StringDim rCnt As Integer
Type the following to create the "CityTbl" table:stringSQL = "CREATE TABLE CityTbl (City TEXT(25), State TEXT(25));"DoCmd.RunSQL (stringSQL)
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 FalseDoCmd.RunSQL (stringSQL)stringSQL = "INSERT INTO CityTbl ([City], [State] ) "stringSQL = stringSQL & "VALUES ('Dallas', 'Texas');"DoCmd.SetWarnings FalseDoCmd.RunSQL (stringSQL)
Type the following to define your query and open your Recordset:stringSQL = "SELECT CityTbl.* FROM CityTbl;"Set RecordSt = dBase.OpenRecordset(stringSQL)
Type the following to create a "For Loop" and display your query results through a message box:RecordSt.MoveFirstFor rCnt = 0 To RecordSt.RecordCount MsgBox (RecordSt.Fields("City").Value & ", " & RecordSt.Fields("State").Value)RecordSt.MoveNextNext rCntPress "F5" to execute the subroutine.