Save sql statement as text file

[bholdman]

We have an Access database that is used by 4 or 5 people. When someone
creates a query we typically use our initials in the name of the query
so that you can keep track of your queries. Sometimes someone else has
already made a query that with a few modifications will work for what
you are needing. The problem is sometimes the criteria is changed and
then the query isn't renamed and is saved, therefore the initial query
no longer works as originally designed.

I would like to create a macro and a button in the toolbar that when
clicked will save the sql statement of the currently opened query to a
text file in a specified directory. I have seen posts on how to export
the results of a query to a file, but not the sql statement.

Thanks,
Brian

 

[Lynn Trapp]

Unfortunately, that facility doesn't exist. The best thing is to copy and
paste the SQL into a text file when you first create the query.

 

[Van T. Dinh]

You can retrieve the SQL of a *saved* Query using the SQL Property of the
QueryDef object. Check SAccess VB Help on the QueryDef object (DAO).

 

[Smartin]

We have an Access database that is used by 4 or 5 people. When someone
creates a query we typically use our initials in the name of the query
so that you can keep track of your queries. Sometimes someone else has
already made a query that with a few modifications will work for what
you are needing. The problem is sometimes the criteria is changed and
then the query isn't renamed and is saved, therefore the initial query
no longer works as originally designed.

I would like to create a macro and a button in the toolbar that when
clicked will save the sql statement of the currently opened query to a
text file in a specified directory. I have seen posts on how to export
the results of a query to a file, but not the sql statement.

Thanks,
Brian

You might be able to make use of the code below to view/save the SQL of
all queries.

Sub ListAllQueries()
Dim QD As QueryDef
For Each QD In DBEngine(0)(0).QueryDefs
Debug.Print QD.Name
Debug.Print QD.SQL
Debug.Print
Debug.Print "================"
Debug.Print
Next
End Sub

 

[bholdman]

This will work if I can get the QueryDef of the currently open query.
Is there a way to get this through code, or do I have to know the name
of a query to get the QueryDef object.

Thanks,
Brian

 

[Douglas J. Steele]

You need to know the name of the query to get its SQL.