Continuing the theme of tools that assist the use of MS Access as a platform for transferring data between systems. Here is a small function that will allow you to write the pure SQL syntax of all queries in a database to a table. I personally used this in a system transfer project. The business had given us something called a field mapping plan that identified the table and fields in one system and where they were to be migrated in the other system. Having written the queries I then wanted to go back through and reconcile the original mapping to the SQL to ensure that absolutely every field had been taken across. Writing the SQL into a table allows for table and field combinations to be methodically searched. Quite useful.
Create a table called T001SQLCollection with at least 2 fields – QueryName and SQL. This is where the recordset writes the SQL to.
This is very much a reverse of the previous post function.
Public Function ListQueries()
Dim rstList As DAO.Recordset
Dim i As Integer
For i = 0 To CurrentDb.QueryDefs.Count - 1
Set rstList = CurrentDb.OpenRecordset("T001SQLCollection")
rstList!QueryName = CurrentDb.QueryDefs(i).Name
rstList!SQL = CurrentDb.QueryDefs(i).SQL