Database Documenter

[bkf7911]

I'm attempting to use the Access database Documenter to provide a report of the SQL used in each query in my database. The report that runs provides the SQL for all queries, but only the name for the first query. It looks to be the report header, and the page header for the rest of the pages includes only the drive location of the DB.
Is there a way to get a report that includes Query name and SQL for all queries in the database?? Any help is greatly appreciated. Thanks.

 

[PHV]

Have a look here:

http://www.jrbarnett.freeserve.co.uk/mdbdoc.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CautionMP]

bkf7911,
The link that PHV provided is a very cool tool. If you want to try doing it within the framework of a standard Access Report you might look at something like this. It's driven by the MSysObjects table and uses a QueryDef object to get the SQL string for a query.

Since it's all in Access you can modify the layout like any other Access report.

[tt]Report
[ul][li]Record Source : SELECT * FROM MSysObjects WHERE Type=5;[/li][/ul]
Detail Section Fields[ul]
[li]Type : TextBox
Name : txtQueryName
Control Source : Name[/li]
[li]Type : TextBox
Name : txtSQL
Can Grow : Yes
Control Source : Blank[/li][/ul][/tt]
Add the following code for the detail format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs(Me.txtQueryName)
Me.txtSQL = qdf.sql
Set qdf = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[bkf7911]

I ended up using the solution explored here (

http://www.access.hookom.net/documenter/).

Then created a database to export the doc_tblObjects, and query/report the results as I needed them to appear. Beat it to fit, paint it to match.....

Kudos to you both for the great assistance though.

 

[dhookom]

Glad to hear the documenter information worked for you.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]