DISCOVER
×

How to Access and Run a Select Query in VBA

Updated July 20, 2017

Microsoft Office Access allows programmers to use Visual Basic for Applications (VBA) to create applications in Access. VBA is a computer programming language used to automate routine tasks in Microsoft Office applications. Access is a relational database system commonly used for smaller databases. A select query is used to retrieve data from a database table. A Recordset is used to read the contents returned by the select query. In a few steps you can write VBA code to create a table and view its contents through a select query.

Open Microsoft Office Access, click "Blank Database," then select "Create." Click "Database Tools," then select "Visual Basic" to open the Microsoft Visual Basic window. Click the "Insert" field and select "Module."

Copy and paste the following code into your new module:

Private Sub runSelectQuery()

Dim dB As Database

Dim rcrdSet As Recordset

Dim strSQL As String

Dim Xcntr As Integer

Set dB = CurrentDb

strSQL = "CREATE TABLE selectQueryData (NumField NUMBER, Tenant TEXT, Apt TEXT);"

DoCmd.RunSQL (strSQL)

strSQL = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "

strSQL = strSQL & "VALUES (1, 'John', 'A');"

DoCmd.SetWarnings False

DoCmd.RunSQL (strSQL)

strSQL = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "

strSQL = strSQL & "VALUES (2, 'Susie', 'B');"

DoCmd.SetWarnings False

DoCmd.RunSQL (strSQL)

strSQL = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "

strSQL = strSQL & "VALUES (3, 'Luis', 'C');"

DoCmd.SetWarnings False

DoCmd.RunSQL (strSQL)

strSQL = "Select selectQueryData.* from selectQueryData "

strSQL = strSQL & "WHERE selectQueryData.Tenant = 'Luis';"

Set rcrdSet = db.OpenRecordset(strSQL)

rcrdSet.MoveLast

rcrdSet.MoveFirst

For Xcntr = 0 To rcrdSet.RecordCount - 1

rcrdSet.MoveNext

Next Xcntr

rcrdSet.Close

db.Close

End Sub

Press "F5" to run your subroutine. The program will create a table, insert data into the table and use a select query to display data for a particular row.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.