Getting SQL Text Out from Access for Reporting/Documentation Purposes

[hasfari]

Hi,

I want to et all of my 260 queries text that I designed
in MS-Access. I can do this manually by visiting each query and viewing its SQL, but is there a better way, such as writing a query to ask Access system objects to spit out all of my queries text.

I appreciate your hint

hasfari

 

[tlbroadbent]

The following VBA code was posted on Tek-Tips in the past. I've tried to find the thread but haven't been able to do so. I don't know who to give credit to for this code.

Public Sub ShowQueryDefs()
On Error GoTo Error_ShowQueryDefs
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb

For Each qdf In db.QueryDefs

Debug.Print qdf.Name & vbCrLf & vbCrLf & qdf.SQL & vbCrLf & vbCrLf

Next

Exit_Error_ShowQueryDefs:
Set qdf = Nothing
Set db = Nothing
Exit Sub

Error_ShowQueryDefs:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Error_ShowQueryDefs
End Sub

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Quehay]

That was me, but it seems that the Debug Window fills up after a certain point. Is there a Char limit to the content for Debug?

Another workaround was to INSERT into a dedicated table (Drop table and handle error for not existing table each time):

[tt]

For Each qdf In db.QueryDefs
strName = Qdf.Name
strDef = Qdf.SQL

strSQL = "INSERT into QDefs(Name, SQL)" & _
"Values (" & Chr(34) & strName & CHR(34) _
& ", " & Chr(34) & strDef & Chr(34) & ""

DoCmd.RunSQL strSQL

Next

[/tt]

This would be great but Access seemed to want to try and run the Query SQL within the variable no matter how I tried to wrap the values. If someone can get around this I'd love to see it--it would be a good way to dynamically document the query objects in the application.

 

[tlbroadbent]

Good point Quehay. The debug window does fill if the database contains a lot of queries. I don't know the limit. I just know that I have hit it.

You could also write the text to to a file. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.