How to export sql of a saved query into text file

[Guest]

How can i export all saved queries sql into a text file or an excel file.

 

[Allen Browne]

You want to programmatically write the SQL statement from all saved queries
to file(s)?

Loop through the QueryDefs of the CurrentDb.
Possibly skip those with a name starting with ~.
Open a file for output.
Print # the SQL property of each QueryDef in the loop.
Close the file.

 

[Guest]

i have saved query by name "QUERY_SELECT_ALL". which contains sql like "
Select * from Tablename".

Like this i have thousand queries saved.

Now i want to get all 1000 queries SQL into a text file.

How can i get it

 

[Allen Browne]

You will need some understanding of VBA code to implement this.

A rudimentary example follows:

Function DumpQueries() As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Const strcFile = "C:\MyQueries.txt"

Open strcFile For Output As #1
Set db = CurrentDb
For Each qdf In db.QueryDefs
If Not qdf.Name Like "~*" Then
Print #1, qdf.Name & ": " & Trim$(Replace(qdf.SQL, vbCrLf, " "))
End If
Next
Close #1
Set qdf = Nothing
Set db = Nothing
DumpQueries = strcFile
End Function