MS Access VBA – Create a Query

The following function can be use VBA to create a query on the fly.

'---------------------------------------------------------------------------------------
' Procedure : CreateQry
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a new query in the current database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sQryName - Name of the query to create
' sSQL - SQL to use
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' CreateQry "qry_ClientList", "SELECT * FROM Clients ORDER BY ClientName"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Nov-07                 Initial Release
'---------------------------------------------------------------------------------------
Sub CreateQry(sQryName As String, sSQL As String)
On Error Resume Next
    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef

    Set db = CurrentDb

    With db
        'In the next line we try and delete the query
        'If it exist it will be deleted, otherwise it will raise an error but since
        'we set our error handler to resume next it will skip over it and continue
        'with the creation of the query.
        .QueryDefs.Delete (sQryName)    'Delete the query if it exists
On Error GoTo Error_Handler             'Reinitialize our standard error handler
        Set qdf = .CreateQueryDef(sQryName, sSQL)    'Create the query
    End With
   
    db.QueryDefs.Refresh  'Refresh the query list to display the newly created query

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: CreateQry" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

4 responses on “MS Access VBA – Create a Query”

    1. Daniel Pineault Post author

      I think you are completely misunderstanding what the post and function are for! They are used to create a query dynamically through VBA. When you call the function, you need to supply 2 input variables: sQryName which will be the name of the query to create and sSQL which is the SQL statement to attribute to this new query.

      So to the question “Where is the query?” – I answer, it does not exist yet, you are creating it. That said, once you create it, it will appear in the Navigation Pane the same as any other query.