How can I export Access Queries as SQL?

Question

How can I export the SQL of my 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 as SQL and it would take quite a lot of time to copy and paste and save them as SQL manually.

Answer 1

I built a little function for you for outputting all the SQL of the queries within your database. Insert the following sub into a module and open your immediate window (ctrl-g?). Add a reference to the Microsoft DAO Object Library if needed.

Public Sub IterateQueryDefsCollection()

Dim dbMain As DAO.Database

Dim qdf As DAO.QueryDef
Dim qdfTemp As DAO.QueryDef

Set dbMain = CurrentDb

For Each qdf In dbMain.QueryDefs
Debug.Print qdf.Name 'Prints name of query
Set qdfTemp = dbMain.QueryDefs(qdf.Name)
Debug.Print qdfTemp.SQL 'Prints SQL Syntax of query
Next

End Sub

Once you have run this, all the SQL should be in your immediate window, just copy and paste to a text file.

Hope that helps! Been there, I build most of my SQL from the Access designer also.

Answer 2

Access 2010 Sql View