In this article I’m going to first show you how to create and run MS Access queries manually, then I’ll build on that by explaining how to automate the execution of these queries via Docmd.OpenQuery. Let’s get started!

Microsoft Access is a database management tool that offers creation and utilization of queries, like you would see in SQL, Oracle, and other similar Database Management Systems (DBMS). In fact, Microsoft Access is very user-friendly in that it has a design view to design all queries. This view is very useful to all users who are not well versed in the query language.  

Available Query Views

Queries can be created either through a query wizard or through a query design in Microsoft Access.

Building a query using design view:

As you can see in the image above, the design view provides a tabular view. The tables to be used in the query can be added from the “Add tables” pane on the right side. In the grid here we can choose the tables, fields, adjust visibility, create conditions etc.

The type of query can be chosen from the Query types listed (highlighted in blue box). According to the type you choose, the options in the grid change.

After creating the query we can save it with a “name” which can be run anytime by just double clicking on it.

Examples of Queries Created Using Design View

Select Query

This is a select query where we select a few columns based on a criterion and display only a few of them.

Let’s select the data of the female employees:

Output upon clicking the Run icon on top:

Upon closing the query, it will give you an option to save the query.

You can give it a name and save:

Update Query

Below is an update query where data is updated based on criteria from two tables.

This query receives grade as an input value when run and it displayed the employees who are teachers for that grade of students.

This is the design view of the query:

Switching to SQL view for a better clarity of what the query does with two tables

Switch to Data🡪 View🡪 SQL View

The queries can be run directly using the run command in the Design tab even if they are not saved.

Points to understand in the query:

The “SELECT” statement contains the list of fields. As the data is selected from more than a table, this is called a “join” query. So, the columns are selected along with the table names i.e. <table name>.<column name / field name>

SELECT [Employee Details].[Employee ID], [Employee Details].[First Name], [Employee Details].[Last Name], [Employee Details].Designation, [Teachers and subjects].Subject, [Teachers and subjects].[Grade Handled]

The “FROM” clause explains the list of tables from which the fields are selected for display. “ON” is used to indicate the common column in the two or more tables based on which data is merged from the tables.

FROM [Employee Details] INNER JOIN [Teachers and subjects] ON [Employee Details].[Employee ID] = [Teachers and subjects].[Employee ID]

The “WHERE” clause explains the conditions that the output needs to be meet. It defines the criteria.

WHERE ((([Employee Details].Designation)="Teacher") AND (([Teachers and subjects].[Grade Handled]) Like "*" & [Enter the Grade] & "*"));

We can also add group by, having, and subqueries to (query within a query/nested query) depending on the requirement.

Output of the query that is run:

So, as per my input, here are the two teachers who teach “2nd Grade. “

Docmd: Running the Created Query Using VBA

There may be instances when you create several queries and need to run them all in a specific order — with a stored procedure or a trigger.  In this case it would be cumbersome to click and run each of these queries individually, remembering their order carefully. Here is where VBA comes to our rescue. We have already seen the use of “Docmd” in one of our articles.

The same Docmd object offers the “OpenQuery” method to run the query from VBA without double clicking on it.

Syntax

DoCmd.OpenQuery(“<name of the query>”)

Where “<name of the query>” is the name of the query we created using the design/sql view. 

Example of Running a Select Query

Using VBA , let us run the select query saved earlier.

Sub openquery_demo()
DoCmd.OpenQuery ("Teachers of specific Grade")
End Sub

The method is the same whether we want to run insert/update/select/append/delete the query designed using the design mode or wizard. All we need is the name of that query.

Example: Running Several Queries at Once

We can use several statements in the order we want to get a desired output.

Let’s consider a sample scenario here:

An online shopping website offers vouchers to customers whose order value is greater than or equal to 1000. But only the latest order is considered for the gift. All other orders will be assumed as duplicate. Another condition is that the customer should have been a member of this online shopping account for the past 365 days. Let us assume that these details are combined from several tables into one.

How to solve:

1. A query to insert only the latest billing records of every unique customer into a new table say “gift list”
2. A query to update the contact details and membership details of the customers (in “gift list” table) from the customer profile table
3. A query to validate the customer for gift based on the bill value and date of membership. This will be updated in a specific column of the “gift list” table.
4. Now we can select the customers who are eligible for the voucher gift using a select query.

Note: In this scenario, it is possible to combine all the above four steps into one big join query with/without subqueries. But here, we are using this scenario to explain an easy way of using the openquery method of the docmd object.

The VBA code for the same would be:

Sub openquery_demo()

' Prepare the list of customers eligible for the voucher gift
DoCmd.OpenQuery ("Unique customers into gift list")
DoCmd.OpenQuery ("Update contact det and member det into gift list from cust profile")
DoCmd.OpenQuery ("Validate customer for gift")
DoCmd.OpenQuery ("Select customers eligible for gift")


End Sub

The output would contain the contact details and names of customers who are eligible for the gift.

Enable and Disable Alerts

As this VBA is run, we will see confirmation alerts, just like how they pop up when we double click on the queries directly. This can be irksome if we are running around 10 or more queries in a sequence. In this case, if we do not want those alerts we can disable them before the queries are run and enable the alerts again as the sub procedure completes running.

Code to Disable Alert Messages:

'set warnings off
DoCmd.SetWarnings False

Code to Enable Alert Messages:

'set warnings off
DoCmd.SetWarnings true

Remember that these statements should be within the sub procedure.

Update or Modify the Query

Any updates or modifications to the query have to be done in the design view of the query and not in the VBA window. Only if the query name is modified, the update should be done in VBA window.

Conclusion

There are pros and cons in using the DoCmd.openquery in VBA.

Pros:

• Comfort and ease designing and modifying queries.
• Run several queries sequentially.
• Run one query at a time easily by double clicking on the design 🡪 run menu. This can help us spot any errors.
• This indirectly acts as a stored procedure for us.

Cons:

• It becomes difficult to search for the queries involving specific tables. However, we can overcome this with a generic query that list the tables and fields used in each query of the database.
• If we don’t work on this regularly, we may not be able to recognize the purpose of a query just by seeing its name. (lol)
• Regarding data security, in general, “Undo” barely does anything in Microsoft Access. This applies to almost all Database Management Systems.