How to Create a Query in Access VBA

Learn how to create a query in Access using VBA.

Knowing how to quickly create a query in Microsoft Access and then use it with Visual Basic for Applications (VBA) can save you time. Access is a relational database management system that is included in the Microsoft Office suite. A query is used to retrieve data from a database table. VBA is a computer programming language that is used to automate routine tasks in Access and other Microsoft Office applications. In a few steps you can create a subroutine using VBA to query data from the Employees table in the Northwind database.

Things You'll Need

• Microsoft Office Access

Instructions

1. Open the Northwind Access database. The Northwind database is included in the Microsoft Office suite.

2. Click "Create" and select "Query Design." Select the "Tables" tab, select "Employees" and then click "Add." Select "Close."

3. Double-click "Company," "Last Name" and "First Name" to add fields. Select "SQL View" in the "Results" pane. Press "Ctrl" and "C" to copy the Select query.

4. Click "Database Tools" and then select "Visual Basic." Type the following to create a new subroutine:

   Private Sub createVBAQuery()

   Press "Enter."

5. Type the following to declare your variables:

   Dim strSQL As String

   Dim rst As Recordset

   Dim dbs As Database

   Set dbs = CurrentDb

6. Press "Ctrl" and "V" to paste the Select query inside the subroutine. Add "strSQL = " to the beginning of your query and add end quotes to the end of your query as follows:

   strSQL = "SELECT Employees.Company, Employees.[Last Name], Employees.[First Name] FROM Employees;"

7. Type the following to open your Select query and display the results.

   Set rst = dbs.OpenRecordset(strSQL)

   rst.MoveLast

   rst.MoveFirst

   Do While Not rst.EOF

   Debug.Print rst.Fields(0).Value & "|" & _

   rst.Fields(1).Value & _

   "|" & rst.Fields(2).Value

   rst.MoveNext

   Loop

References

• Microsoft: Tips and Techniques for Queries in Access