[AccessD] (Tip) Locate a Table Name within Queries

Here is some code that can be useful for letting you know which queries use a certain table.  Cut and past this into a module.


Option Compare Database
Option Explicit

Sub findtbls()
FindAllQueriesThatContainASpecifiedTable ("MyTableNameHere")
End Sub
 
Public Function FindAllQueriesThatContainASpecifiedTable(tblName As String)
    Dim qd          As QueryDef
    Dim DB          As Database
    Dim tb          As TableDef
    Dim rsQueries   As Recordset, RS    As Recordset
    Dim sqlStr      As String
    Dim index       As Integer
    Set DB = CurrentDb()
    Set tb = DB.CreateTableDef("tempTblDef")      'create temp table
    sqlStr = "SELECT QueryName FROM " & tb.name & " WHERE ((([QuerySQL]) LIKE '*" & tblName & "*'))"
                                                  
    tb.Fields.Append tb.CreateField("QueryName", dbText) 'add fields we need
    tb.Fields.Append tb.CreateField("QuerySQL", dbMemo)
    DB.TableDefs.Append tb
    
    Set RS = tb.OpenRecordset()                       'open the table!
    
    For Each qd In DB.QueryDefs                     'get sql and name of each query, ignoring
        If (Left(qd.name, 1) <> "~") Then           'system and hidden queries
            RS.AddNew
            RS!QueryName = qd.name
            RS!QuerySQL = qd.SQL
            Debug.Print RS!QueryName
            RS.Update
        End If
    Next
    RS.close
    
    'open up a recordset based on the temp table using a SQL query
    Set rsQueries = DB.OpenRecordset(sqlStr)
    rsQueries.MoveLast: rsQueries.MoveFirst
    
    'print out the resuls of our query
    Debug.Print "------ Queries containing table '" & tblName & "' -------"
    Debug.Print "------ number of queries : " & rsQueries.RecordCount
    For index = 1 To rsQueries.RecordCount Step 1
        Debug.Print rsQueries!QueryName
        rsQueries.MoveNext
    Next
    
    rsQueries.close                                 'get rid of it!
    DB.TableDefs.Delete tb.name                     'delete temp. tabledef
    Set DB = Nothing
End Function




Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us




 




---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030725/71bd8286/attachment-0001.html>