Database Topics: Creating a Query

	Creating a Query

Description

To programmatically create a query using either the Microsoft Access Object Library or DAO, you can use the CreateQueryDef() method of the Database object. The syntax of this method is:

CreateQueryDef(NewQueryName, SQLStatement)

The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query.

Once a query has been created and saved, it becomes a database object like the others we have used so far. If a query exists already, to use it, the user can open it like a table. To programmatically open a query, you can call the OpenQuery() method of the DoCmd object. This method takes one string argument as the name of the query.

Here is an example:

Private Sub cmdOpenVideoTitles_Click()
    DoCmd.OpenQuery "VideoTitles"
End Sub

After using a query, the user can close it like a regular window by clicking its system Close button. To programmatically close a query, you can call the Close() method of the DoCmd object, passing the first argument as acQuery and the second argument as the name of the query. Here is an example:

Private Sub cmdCloseVideoTitles_Click()
    DoCmd.Close acQuery, "VideoTitles"
End Sub

When this method is called, it checks whether the query is opened. If a query with that name is opened, it would be closed. If no query with that name is opened, nothing would happen.

Examples

Here is an example that creates a query after using a SELECT statement to select the necessary fields from a table:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As DAO.Database
    Dim qryEmployees As DAO.QueryDef
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SELECT FirstName, LastName FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub