Where used?

[meagain]

Hi All,

How can I find out where a table is used within an application? It is a 10 year old application with over 2700 queries alone, and I want to make sure an existing table with the name I want to use (for consistency purposes) is not being used elsewhere, so that I can replace it.

Thanks,
Lori

 

[sfm6s524]

This might be a start for your queries. Type the code below into a module, then in the Immediate Window (Ctrl-G) type ? TableInQuery

2,700 queries? That may be a record!
Ken

Function TableInQuery()

Dim DB As DAO.Database
Dim QryDef As DAO.QueryDef
Dim X As Long
Dim FindThisTable As String

FindThisTable = InputBox("Enter table name to search for in all queries: ", "Table To Find")
If FindThisTable <> "" Then
Set DB = CurrentDb
For X = 0 To DB.QueryDefs.Count - 1
Set QryDef = DB.QueryDefs(X)
If InStr(QryDef.SQL, FindThisTable) > 0 Then
Debug.Print QryDef.Name
End If
Next X
End If

End Function

 

[MajP]

This is basically a modification to the provided code by SFMS. It handles table names with spaces, and does not include a table name that is part of another table name (tblOrders versus tblOrdersDetails). Basically the same idea.

Public Function tblInQueries(strTblName As String) As String
  Dim strTblName2 As String
  Dim strDocument As String
  Dim strSql As String
  Dim qryDef As DAO.QueryDef
  strTblName = " " & strTblName & " "
  strTblName2 = " [" & strTblName & "] "
  strDocument = strTblName & ":" & vbCrLf
  For Each qryDef In CurrentDb.QueryDefs
    strSql = qryDef.SQL
    If Nz(InStr(1, qryDef.SQL, strTblName, vbTextCompare), 0) <> 0 Or Nz(InStr(1, qryDef.SQL, strTblName2, vbTextCompare), 0) <> 0 Then
      strDocument = strDocument & vbCrLf & qryDef.Name & vbCrLf & "    " & qryDef.SQL
    End If
  Next qryDef
  tblInQueries = strDocument
End Function

you will likely see queries with ~ in front of them. These are queries such as used in a combo box or subform but not a saved query. If you read the query information you will be able to figure out what control it is used in.