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

Here is a shorter version using the system tables. This works for Access97
and because it uses the system tables may be less portable to newer versions
of Access. The advantage is that it doesn't require making any new (although
temporary) tables...
 
Option Compare Database
Option Explicit
 
Sub findtbls()
FindAllQueriesThatContainASpecifiedTable ("MyTableNameHere")
End Sub
 
Public Function FindAllQueriesThatContainASpecifiedTable(tblName As String)
    Dim DB          As Database
    Dim rsQueries   As Recordset
    Dim sqlStr      As String
    Dim index       As Integer
    
    Set DB = CurrentDb()
        
    sqlStr = "SELECT MSysObjects.Name AS QueryName " & _
             "FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId " & _
             "WHERE (((MSysQueries.Name1)=""" & tblName & """) AND
((MSysQueries.Attribute)=5));"
                                                      
    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
    Set DB = Nothing
End Function

 
Scott

-----Original Message-----
From: Lonnie Johnson [mailto:prodevmg at yahoo.com]
Sent: Friday, July 25, 2003 8:26 AM
To: Access Developers discussion and problem solving
Subject: [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 <http://www.prodev.us/> 


 <http://www.galaxymall.com/software/PRODEV> 

 



  _____  

Do you Yahoo!?
Yahoo!  <http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com>
SiteBuilder - Free, easy-to-use web site design software

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030725/9107971a/attachment-0001.html>