Which queries are relying on a specific table?
This query in Access will return a list of saved queries that select from the table you specify:
SELECT DISTINCT MSysObjects.Name
FROM MSysQueries
INNER JOIN MSysObjects
ON MSysQueries.ObjectId=MSysObjects.Id
WHERE (((MSysQueries.Name1) Like "*" & [TableName] & "*")) OR (((MSysQueries.Name2) Like "*" & [TableName] & "*"));
This query can also be used to search for queries that return a specific table field:
SELECT DISTINCT MSysObjects.Name, MSysQueries.*
FROM MSysQueries
INNER JOIN MSysObjects
ON MSysQueries.ObjectId=MSysObjects.Id
WHERE MSysQueries.Expression='[**Tablemaster**].[Birth Date]'
Some of our queries got all their fields renamed to Expr1, Expr2, etc. Here is how we wrote a VBA script to remove those field renames:
Sub QueryDefX()
Dim qdfLoop As QueryDef
Dim strOrigSQL As String
Dim strNewSQL As String
Dim intResp As Integer
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
' Enumerate QueryDefs collection.
For Each qdfLoop In CurrentDb.QueryDefs
' process queries if the SQL includes the string "Expr"
If InStr(qdfLoop.SQL, "Expr") > 0 Then
strOrigSQL = qdfLoop.SQL
' Remove the renames to Expr1, Expr2, etc.
RegEx.Pattern = " AS Expr[0-9]*"
RegEx.Global = True
strNewSQL = RegEx.Replace(strOrigSQL, "")
' Show the original and altered
MsgBox qdfLoop.Name
MsgBox strOrigSQL
MsgBox strNewSQL
' Ask user for permission, and rewrite the query if it's granted
intResp = MsgBox("Alter " & qdfLoop.Name & "?", vbYesNoCancel + vbQuestion, "Save?")
If intResp = vbYes Then
qdfLoop.SQL = strNewSQL
ElseIf intResp = vbCancel Then
Exit For
End If
End If
Next qdfLoop
End Sub