Make a new query or change the SQL of a query using VBA

Quick Jump

Download

Download zipped BAS file you can import into your VBA projects for Access. mod_Query_Make_s4p.zip

If you have trouble with a download, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

VBA

Standard module

' module name: mod_Query_Make_s4p
'*************** Code Start ***************************************************
' Purpose  : make a query or change the SQL of a query
' Author   : crystal (strive4peace)
' Code List: www.msaccessgurus.com/code.htm
' This code: https://msaccessgurus.com/VBA/Query_Make.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
'--------------------------------------------------------------------------------
'                              Query_Make_s4p
'--------------------------------------------------------------------------------'
Sub Query_Make_s4p( _ 
   ByVal qName As String _ 
   ,ByVal pSql As String _ 
   ) 
'crystal (strive4peace) 220127
' if query already exists, update the SQL
' if not, create the query

   On Error GoTo Proc_Err 
   
   Dim db As DAO.Database 
   Set db = CurrentDb 
   
   With db 
      'Query: Type = 5  
      If Nz(DLookup( "[Name]", "MSysObjects", _ 
          "[Name]='" & qName _ 
          &  "' And [Type]=5"), "") =  "" Then 
          .CreateQueryDef qName,pSql 
      Else 
         'if query is open, close it
         On Error Resume Next 
         DoCmd.Close acQuery,qName,acSaveNo 
         On Error GoTo Proc_Err 
         .QueryDefs(qName).SQL = pSql 
      End If 
      .QueryDefs.Refresh 
      'refresh database window
      Application.RefreshDatabaseWindow 
   End With 
   
Proc_Exit: 
   On Error GoTo 0 
   Set db = Nothing 
   Exit Sub  
   
Proc_Err: 
   MsgBox Err.Description,, _ 
     "ERROR " & Err.Number &  "  Query_Make"
    
   Resume Proc_Exit 

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume 
End Sub 
'*************** Code End *****************************************************

Reference

Microsoft

QueryDef.SQL property (DAO)

Database.QueryDefs property (DAO)

CreateQueryDef method (DAO)

QueryDefs.Refresh method (DAO)

Application.RefreshDatabaseWindow method (Access)

Backstory

Saved queries can be used as record sources and row sources, as well as opening and viewing or changing data.

I use this procedure a lot! You can also use it for modifying the SQL for a pass-thru query. The next post will be VBA to create an SQL statement that links to a CSV text file.

If you like this page, please let me know. Donations, big and small, are much appreciated! They all say Thank you.

Share with others

Get Help with Access and connecting to data

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Access is great at many things, and connecting to data from all kinds of different places. Access is access! I'd love to help you. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see