How to Access and Run a Select Query in VBA

Written by jaime avelar | 13/05/2017
How to Access and Run a Select Query in VBA
(Polka Dot RF/Polka Dot/Getty Images)

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.

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.