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

[mallik]

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

 

[Marshall Barton]

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?

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