How to get the sql script from Query and Report objects in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an access database file containing many queries and report objects
that have been written. I like to know if it is possible to get the sql
script written behind the objects programmatically using the Access object
hierarchy? Thanks in advance.

Mallik
 
mallik said:
I have an access database file containing many queries and report objects
that have been written. I like to know if it is possible to get the sql
script written behind the objects programmatically using the Access object
hierarchy?
Click to expand...


You can get the SQL statement of any QueryDef through its
SQL property.

Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
For Each qdf In db.QueryDefs
Debug.Print qdf.SQL
Next qdf

For Forms/Reports, you need to open the object in design
view and extract its RecordSource property. Then analyse it
to see if it starts with an SQL keyword (e.g. Select,
Parameter, Transform), if not, then check if it's a query
name.

Dim doc As Document
For Each doc In db.Containers!Reports.Documents
DoCmd.OpenReport doc.Name, acViewDesign
With Reports(doc.Name)
If .RecordSource Like "SELECT *" _
OR .RecordSource Like "Parameters *" _
OR .RecordSource Like "TRANSFORM *" Then
Debug.Print .RecordSource
Else
For Each qdf In db.QueryDefs
If .RecordSource = qdf.Name Then
Debug.Print qdf.SQL
Exit For
End If
Next qdf
End If
End With
Next doc
 
Back
Top