Find All Queries That Use a Particular Table
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
Debug.Print qdf.Name
End If
Next qdf
Set qdf = Nothing
MsgBox “Search Completed”
Exit Function
ErrorHandler:
If Err.Number = 3258 Then
strSQL = “”
Resume
End If
End Function
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:
?SearchQueries("tblCustomers")
The system will return all the queries where used in the same immediate window:
qryCustomerList
qryOrdersByCustomer
qryOrderHistory
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!
This routine may give you false positives if you have tables named “tblCustomers” and “tblCustomers_Orders”. One way around that could be to pad the search with a space on each side. Then it should at least find the table name in the From clause.
InStr(1, strSQL, ” ” & Trim(strTableName) & ” “)
or, just include spaces when you call this function.
Thanks Ben!
I used this code to track which FUNCTIONS are used in queries. This was very helpful as when functions change the query might need to be updated so I was able to quickly find them an update them.
The only thing I did was change strTableName to FunctionName and add a ( at the end so it knows it’s a function, worked perfectly.
FunctionName = FunctionName & “(“
Absolutely brilliant. This is exactly what I needed and gives with no fuss. Thank you!
instead
If InStr(1, strSQL, strTableName) > 0 Then
should be
If InStr(1, strSQL, strTableName) Then
HTML code is got stuck in it
on line below
If InStr(1, strSQL, strTableName) > 0 Then
> 0 — need remove from the Code, it works really good, thank you!
Very good, thanks. A good alternative to using the in-built linkage tool.
Very smart and useful function! I like it.
Thank you for sharing it!
Thank you so much. This is a huge help for debugging systems. When I tried to use the code I did get an error on the line… If InStr(1……). It seemed to have a problem with the >. I’m not familiar enough with the syntax to know why. Should this result in an error or am I doing something incorrect?
Thanks
A good and useful example !!!
The simplest little clusters of code can result in such powerful information making huge tasks so easy. Thanks Juan!