Programmatically capturing query SQL

[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.

 

[Kevin B]

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...

 

[Dirk Goldgar]

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.

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

 

[Kevin B]

Thanks Dirk, it worked like a charm.