This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method.
You may also want to read:
Access VBA create Query
Sometimes we don’t want to create Query in Query Design View, because you may create a Query with criteria depending on a variable. To create Query in Access VBA, you can directly write a SQL statement in Access VBA, and then create a Query using the SQL statement with CreateQueryDef Method.
Syntax of CreateQueryDef
expression.CreateQueryDef(Name, SQLText)
| Name | Required/Optional | Data Type | Description |
|---|---|---|---|
| Name | Optional | Variant | A Variant (String subtype) that uniquely names the new QueryDef. |
| SQLText | Optional | Variant | A Variant (String subtype) that is an SQL statement defining the QueryDef. If you omit this argument, you can define the QueryDef by setting its SQL property before or after you append it to a collection. |
Example – Access VBA create Query
The below Procedure creates a new Query called tempQry.
Private Sub createQry()
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL As String
newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
Set qdf = db.CreateQueryDef("tempQry", newSQL)
End Sub
It is important to note that if you have string criteria in the SQL statement, you can use single quote instead of double quote.
If your SQL criteria is a variable, you can use & to join the statement. For example, you want to use variable depart instead of ‘HR’ in the [Department] criteria.
newSQL = "Select * From [employee_tbl] WHERE [Department]=" & depart
Error Handling
If you already have a Query called tempQry, when you run the Procedure to create Query of the same name, you will receive an error message.
You can add an error handling to delete the Query if it already exists.
Private Sub createQry() Dim db As DAO.Database Set db = CurrentDb Dim qdf As DAO.QueryDef Dim newSQL As String On Error Resume Next DoCmd.DeleteObject acQuery, "tempQry" On Error GoTo 0 newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'" Set qdf = db.CreateQueryDef("tempQry", newSQL) End Sub
Run the new Query
At the end of the Procedure, you can use OpenQuery Method to open the Query.
DoCmd.OpenQuery ("tempQry")