Create a VBA to run a Query in MS Access

Need to create a VBA to run a query in MS Access?

If so, I’ll show you the steps to add a VBA to run a query when clicking a button.

In general, here is the VBA syntax that you can use to run a query in MS Access:

DoCmd.OpenQuery "Query Name"

Please note that the “Query Name” should NOT be inserted within brackets “[]”

In the next section, I’m going to show you the steps that you can use in order to create a VBA to run a query using a click of a button.

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 MS Access Form

First, open MS Access, and then add an Access Form. You can add an Access Form by going to the CREATE tab, and then pressing on the Form Design icon located on the MS Access Ribbon:

Form Design - MS Access

Step 2: Place a Button

Place a button on the Form itself. You can do that by clicking on the ‘button icon’ on the Access Ribbon. Then, place that button on the Form itself:

Create a VBA to run a Query in MS Access

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: Select the Event Procedure

If the Property Sheet is not already populated at the right-hand-side of your screen, then press F4 to get that screen (i.e., press F4 while the button is selected).

On the Property Sheet select the ‘[Event Procedure]’ associated with the ‘On Click’ option:

Create a VBA to run a Query in MS Access

Step 4: Open the VBA Screen

To open the screen where you can write your VBA for your button, press on the 3 dots ‘…’ corresponding to the ‘On Click’ option under the Property Sheet:

Property Sheet in MS Access

Step 5: Write the VBA to Run the Query

Write the VBA syntax to run your query upon a click (here, the query’s name is “max_sales”). The syntax should be as follows:

Private Sub Command0_Click()

DoCmd.OpenQuery "max_sales"

End Sub

This is how the VBA would look like:

VBA to run query in MS Access

Step 6: View the Results

Once you’re done writing the VBA syntax, 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:

Form View in MS Access

Finally, press on the button that is located on your Access Form to run the “max_sales” query upon a click:

Form button - MS Access

You can now run your query at any given time by simply pressing on the button.