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:


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>

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


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

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/MS_Access_Professionals/join
    (Yahoo! ID required)

<*> To change settings via email:
    MS_Access_Professionals-digest@yahoogroups.com
    MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/



__._,_.___

Posted by: Crystal <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)

.

__,_._,___

Tidak ada komentar:

Posting Komentar