Access VBA Query

In this tutorial, you will learn how to how to open and run queries in VBA.

We have created a table called ProductsT, and a simple select query which selects the product category where the product name is Product BBB, from the table.

Opening a Query with VBA

You can use the DoCmD.OpenQuery method to open/view a query that you have created. The following code will open the query called QueryOne:

DoCmd.OpenQuery "QueryOne"

The result is:

The syntax of the DoCmd.OpenQuery method is:

DoCmd.OpenQuery (QueryName, View, DataMode) where:

Parameter	Description
QueryName	The name of an existing query in the database that you'd like to open/view.
View	The view that you'd like to open the query in. This can be acViewDesign, acViewLayout, acViewNormal, acViewPivotChart, acViewPivotTable, acViewPreview or acViewReport. The default is acViewNormal. (Optional)
DataMode	The data entry mode that you would like to open your query in. This can be acAdd, acEdit or acReadOnly. The default is acEdit. (Optional)

Running Action Queries with VBA

You can use the DoCmd.RunSQL method to run action queries such as update queries, append queries, delete queries and make table queries. The following code appends a record to the table:

DoCmd.RunSQL "Insert INTO ProductsT ([ProductID],[ProductName],[ProductCategory],[ProductPricePerUnit]) VALUES (6,'Product FFF','Toys',10);"

The syntax of the DoCmd.RunSQL method is:

DoCmd.RunSQL SQLStatement where:

SQLStatement – required and is a SQL Statement that either updates records, appends records, deletes records or makes a new table.

Note: DoCmd.RunSQL only works with certain SQL statements.