Need to create a VBA to run a query in MS Access?
If so, I’ll show you the full steps to accomplish this task using a button click.
In general, here is the VBA syntax that you may use to run your query in MS Access:
DoCmd.OpenQuery "Query Name"
Please note that the “Query Name” should NOT be inserted within brackets “”
Steps to Create a VBA to Run a Query in MS Access
Let’s suppose that you already have an Access database, and that the Access database contains a query called “max_sales”
Here are the steps that you’ll need to follow in order to create a VBA to run the “max_sales” query:
Step 1: Add an Access Form
To begin, open MS Access, and then add an Access Form.
You can add an Access Form by navigating to the Create tab, and then pressing on the Form Design icon located on the MS Access Ribbon:
Step 2: Place a Button
Next, place a button on the Form itself. To do that, click on the ‘button icon’ on the Access Ribbon, and then place that button on the Form:
After you placed the button on the Form, you’ll notice that the Command Button Wizard would appear on your screen. Press Cancel as the Command Button Wizard will not be needed for our purposes.
Step 3: Open the VBA Screen
To open the screen where you can write your VBA for your button, right-click on the button, and then select ‘Build Event…‘
Next, select the Code Builder option, and then click on OK:
Step 4: Write the VBA to Run the Query
For this step, write the VBA to run the query.
Here is the complete VBA for our example (where the query’s name is “max_sales”):
Private Sub Command0_Click() DoCmd.OpenQuery "max_sales" End Sub
And this is how the VBA would look like in Access:
Step 5: View the Results
Once you’re done writing the VBA, save your work. Then, go back to your Access Form and switch to the Form View.
To switch to the Form View, press on the View icon under the Design tab. Then, select Form View from the drop-down list:
Finally, press on the button to run the “max_sales” query:
You can now run your query at any given time by simply clicking the button.