Back To Top

MS Access VBA Get Query's SQL string for versions 2000, 2002, 2003, 2007

Go here To Check if Table exists in VBA.
Go here To Check if Field exists in VBA.
Go here To Check if Query Exsists in VBA.


The following code snippet will use VBA to retrieve the SQL string from a Query.
The VBA Get Querie's SQL Function is available for download here in a zipped MS Access Database.


Function
GetQuerySQL(QueryName As String ) As String
  
' References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   ' Set references by Clicking Tools and Then References in the Code View window
   ' Retrieves the SQL property of a Query
   '
   ' QueryName: Name of the Target Query
   '
   ' Returns a Query's SQL string
   ' USAGE: getQuerySQL ("QueryName")

  
On Error GoTo errhandler

  
Dim db As Database
  
Dim qdf As QueryDef
  
Dim prp As DAO.Property

  
Set db = CurrentDb
  
Set qdf = db.QueryDefs(QueryName)

  
With qdf
     
' Print the SQL text to the Immediate window
      ' You could parse the text and retrieve Table Names and Fields
      ' You could use the string and Execute the SQL in VBA or whatever you need
     
Debug.Print qdf.SQL

  
End With


ExitHere:
  
Set prp = Nothing
   Set
qdf = Nothing
   Set
db = Nothing

  
' Notify the user the process is complete.
  
MsgBox "Get Query's SQL Complete"
  
Exit Function

errhandler:
  
' There is an error return false

  
With Err
      MsgBox
"Error " & .Number & vbCrLf & .Description, _
            vbOKOnly
Or vbCritical, "getQuerySQL"
  
End With

   Resume
ExitHere
End Function



For the above VBA Get a Query's SQL string Function in a usable Form with Button, download the Sample database in ZIP format. The sample includes 3 tables and 1 Query to test on.


You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.



For further MS Access tutorials go here: MS Access tutorials
Follow the Set Autonumber Field with VBA Link to Set a Field to Autonumber.

 

Did you find what you were looking for?
What would you suggest?
mail@eraserve.com
Your Name:
Your Message:
Your Email:

    

 

Custom Search