ACCESS FEED: Re: [MS_AccessPros] Q: Utility to extract SQL from Access 2003

Jumat, 24 April 2015

Re: [MS_AccessPros] Q: Utility to extract SQL from Access 2003

adding on ...

here is some code to write all the SQL from all queries to a searchable Word document:

Document Query SQL, Form and Report RecordSource
http://www.rogersaccesslibrary.com/forum/document-query-sql-form-and-report-recordsources_topic606.html

Warm Regards,

Crystal

~ have an awesome day ~

On Friday, April 24, 2015 12:44 PM, "Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

BRILLIANT!

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 24, 2015 11:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Q: Utility to extract SQL from Access 2003

Gary-

You could export all the query SQL as a text file like this:

Dim db As DAO.Database, qdf As DAO.QueryDef Dim intI As Integer, strObjName As String

Set db = CurrentDb
' Open an output file
Open CurrentProject.Path & "\AccessSQL.txt" For Output Access Write As #1
' Loop to process them all
For intI = 0 To db.QueryDefs.Count - 1
' Point to the query
Set qdf = db.QueryDefs(intI)
' Save the name for debug
strObjName = qdf.Name
' Skip temp system queries
If Left(qdf.Name, 1) <> "~" Then
' Output the query name
Print #1, strObjName
' Output the SQL
Print #1, qdf.SQL
' Add a space
Print #1, " "
End If
' Do the next one
Next intI
Set qdf = Nothing
Close #1

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Apr 24, 2015, at 7:54 PM, 'Gary D. Schwartz' gary.schwartz@pobox.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi:

I have an older db in Access 2003 with about 3000 queries.

I am looking for a method to extract the Access SQL code from all of the queries. I know how to do this one at a time, but . . .

Complicating this is that some utilities will truncate to fit 255 characters, which is a problem for some queries. Outppuit to tex CSV or to html would be preferable.

Thanks,
Gary

--------------------------------------------------------
Gary D. Schwartz Please reply to: gary.schwartz@pobox.com

--------------------------------------------------------
Gary D. Schwartz Please reply to: gary.schwartz@pobox.com

------------------------------------
Posted by: "Gary D. Schwartz" <gary.schwartz@pobox.com>
------------------------------------

------------------------------------

Yahoo Groups Links

------------------------------------
Posted by: John Viescas <johnv@msn.com>
------------------------------------

------------------------------------

Yahoo Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

------------------------------------
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>