Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / September 2006

Tip: Looking for answers? Try searching our database.

How to export sql of a saved query into text file

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Madhukar - 02 Sep 2006 07:51 GMT
How can i export all saved queries sql into a text file or an excel file.
Allen Browne - 02 Sep 2006 08:44 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> How can i export all saved queries sql into a text file or an excel file.
Madhukar - 03 Sep 2006 11:25 GMT
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

> You want to programmatically write the SQL statement from all saved queries
> to file(s)?
[quoted text clipped - 6 lines]
>
> > How can i export all saved queries sql into a text file or an excel file.
Allen Browne - 03 Sep 2006 13:03 GMT
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

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> i have saved query by name "QUERY_SELECT_ALL". which contains sql like "
> Select * from Tablename".
[quoted text clipped - 17 lines]
>> > How can i export all saved queries sql into a text file or an excel
>> > file.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.