Access VBA create Query using CreateQueryDef

This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method.

You may also want to read:

Change Query criteria

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")

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *