Link to home
Start Free TrialLog in
Avatar of Paulconsulting
Paulconsulting

asked on

How can I export Access Queries as SQL?

How can I export the SQL statement of access queries? When I right click a query and select 'Design View', then right click the top frame and choose 'SQL View' I get the SQL of the query. Its this that I need to export. I have quite a lot of queries that I need the SQL statement and it would take quite a lot of time to copy and paste and save them as .SQL manually.
Avatar of jss1199
jss1199

Hi Paulconsulting,

The poster at the below link built an Access function to export SQL for all the queries contained within an access db.  I've used it several times with success.

Enjoy!
jss1199
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Another way might be DBDocumentor

U might be able to get all SQL's dumped in a report
Yep, go to Tools/Analyze/Documentor
go to Queries tab, click on Options for more info on what to dump

Avatar of Paulconsulting

ASKER



rockiroads, how is this used?



Public Sub ExportQueries()

    Dim qdf As dao.QueryDef
    Dim iHandle As Integer
   
    For Each qdf In CurrentDb.QueryDefs
        If Left$(qdf.Name, 1) <> "~" Then
            Debug.Print qdf.Name
            iHandle = FreeFile
            Open "C:\EE\" & qdf.Name & ".sql" For Output As iHandle
            Print #iHandle, qdf.SQL
            Close (iHandle)
        End If
    Next
End Sub
You place it in a module then just run it

If u wanted it called from a form, say  a cmd btn, u can use the click event just to call ExportQueries

as a test. add this code to a module (dont save as ExportQueries though!) then click anywhere in that code then hit F5
u may want to change parent directory C:\EE\ to wherever u want it dumped.

I have to go now, so any more questions, I wont be able to respond until much later

Good luck
When I run that I get the following error:



Compile Error:
user-defined type not defined


with qdf As dao.QueryDef highlighted in the code
You need a reference to DAO

TOOLS > REFERENCES  Microsoft DAO Object Library 3.x
Thanks, got it.