We just completed a massive 39 database conversion to SQL Server for a client in Tennessee which was quite challenging, we had to not only upsize all the tables, but switch the system from importing/exporting between databases to using linked tables instead. At times we needed to find which queries was using a table, so we used the following code to quickly find a table in all of the queries:
Public Function SearchQueries(strTableName As String)
Dim qdf As DAO.QueryDef
Dim strSQL As String
On Error GoTo ErrorHandler
For Each qdf In CurrentDb.QueryDefs
Application.Echo True, qdf.Name
strSQL = qdf.SQL
If InStr(1, strSQL, strTableName) > 0 Then
Set qdf = Nothing
MsgBox "Search Completed"
If Err.Number = 3258 Then
strSQL = ""
Let’s say we need to find where tblCustomers is being used in the query collection, we would press Ctrl-G to go to the Immediate window and type:
The system will return all the queries where used in the same immediate window:
I hope you find this code helpful! Let me know in the comments, anyone leaving a comment will be entered into a $25 Amazon.com gift certificate, hurry, contest ends 6/10/16!