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.
Paulconsulting,
And NOW the link.....
http://answers.yahoo.com/question/index?qid=20061206063744AAy4eCd
And NOW the link.....
http://answers.yahoo.com/question/index?qid=20061206063744AAy4eCd
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another way might be DBDocumentor
U might be able to get all SQL's dumped in a report
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
go to Queries tab, click on Options for more info on what to dump
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
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
ASKER
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
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
TOOLS > REFERENCES Microsoft DAO Object Library 3.x
ASKER
Thanks, got it.
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