MS Access Query Code

[anasser]

I was wondering how can I get all the sql code from my queries in my
Access Database and put the code in a text file.

I have around 406 queries in my Access Database, and I will try to have
all these queries inbedded in my VBA code instead of having query
calls. I need to get first the sql code for all these queries with the
query names in one text document to make it easier to check many
things, like some of the queries are not even used anymore, so I would
be able to pinpoint these queries and not include them in my vba code.

Thanks for any ideas and approaches to this.

 

[Douglas J Steele]

Why do you want to switch from queries to inline code? There's no advantage
(and under the covers, Access actually creates a query object anyhow...)

Anyhow, do you want the code in one file, or multiple?

The following code uses the undocumented SaveAsText approach to save each
query into its own file:

Dim dbCurr As DAO.Database
Dim intLoop As Integer

For intLoop = 0 To dbs.QueryDefs.Count - 1
Application.SaveAsText acQuery, _
dbCurr.QueryDefs(intLoop).Name, _
"C:\Document\" & dbCurr.QueryDefs(intLoop).Name & ".txt"
Next intLoop

Set dbCurr = Nothing

To get them all into one file, you could use something like:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim intFile As Integer
Dim strFile As String

strFile = "C:\Document\AllQueries.txt"
intFile = FreeFile()
Open strFile For Output As #intFile

Set dbCurr = CurrentDb()

For Each qdfCurr In dbCurr.QueryDefs
Print #intFile qdfCurr.Name & vbCrLf
Print #intFile qdfCurr.SQL & vbCrLf & vbCrLf
Next qdfCurr

Close #intFile
Set dbCurr = Nothing

 

[Mike Labosh]

I was wondering how can I get all the sql code from my queries in my

Access Database and put the code in a text file.

Private Sub listAllQueries()

' Creates C:\QueryNames.txt, containing the names
' and SQL content of each query in your database.
' remember, this will also give you the hidden or
' system queries also. The text file will be
' delimited like this: "name", "sql"
' Then you can import the txt file into a table
' and make a simple form on that table for scrolling
' and viewing.

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim ff As Integer

Set db = CurrentDb

ff = FreeFile
Open "C:\QueryDef.txt" For Append Access Write Lock Read Write As ff

For Each qd In db.QueryDefs
Write #ff, qd.Name, qd.sql
Next

Close #ff
Set qd = Nothing
Set db = Nothing

End Sub

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

 

[anasser]

Thanks to all of you for the prompt response...

Douglas J Steele, I am trying to organize the whole application
(Backend and Frontend databases) and fix it. I did not develope this
database and it was developed over a long period of time started with
Access 97 till now using Access 2002, so it is kindda more practical to
do searches on which query calling which query when the syntax of all
the queries together is located in one text file. Plus, in the code
(of this very much used and complicated database), there are many VBA
versions for connecting between Access and the SQL Server, there is
even switches in the VBA code within a single procedure to connect
using ADO from using DAO and Visa Versa. My "maybe" future vision is
to convert the whole application database to VB .NET and use Datasets
between the application and the Server using XML files.

Mike Labosh, I like your foot notes.