MS Access VBA Function – Loop through Query Objects and write SQL to Table

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")
With rstList
.AddNew
rstList!QueryName = CurrentDb.QueryDefs(i).Name
rstList!SQL = CurrentDb.QueryDefs(i).SQL
rstList.Update
End With
Next i

rstList.Close

MsgBox "Finished"

End Function

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, MS Access, VBA Code MS Access. Bookmark the permalink.