Programmatically capturing query SQL

  • Thread starter Thread starter Kevin B
  • Start date Start date
K

Kevin B

Is there a way to cycle through the documents in the tables container and if
the table is a query document capture the query SQL and write the query name
and the SQL for that query back to a table?

I'm using the query prefix tag (qsel, qapp, qxtb, etc...) to differentiate
between table objects and query object.

The query data is going to be part of a data dictionary as the Access app
will be migrated to SQL server sometime later in the year.
 
I do know that I can capture this with the database documenter, I was just
wondering if it can be done in another manner.

Thanks...
 
Kevin B said:
Is there a way to cycle through the documents in the tables container and
if
the table is a query document capture the query SQL and write the query
name
and the SQL for that query back to a table?

I'm using the query prefix tag (qsel, qapp, qxtb, etc...) to differentiate
between table objects and query object.

The query data is going to be part of a data dictionary as the Access app
will be migrated to SQL server sometime later in the year.
Click to expand...


It's easiest to do this using DAO and the QueryDef object:


Dim db As DAO.Database
Dim qdf As QueryDef

Set db = CurrentDb

For Each qdf In db.QueryDefs
With qdf
If Left$(.Name, 3) <> "~sq" Then
Debug.Print "Query " & .Name & " SQL: " & .SQL
End If
End With
Next qdf

Set db = Nothing
 
Back
Top