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

Here's a sliightly shorter way to do it directly in a parameterised
query:
 
PARAMETERS [Enter table name] Text ( 255 );
SELECT MSysObjects.Name, IIf(IsNull([Expression]),[Name1],[Name2] & "("
& [Expression] & ")") AS SourceTable
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((IIf(IsNull([Expression]),[Name1],[Name2] & "(" & [Expression] &
")"))=[Enter table name]) AND ((MSysObjects.Type)=5) AND
((MSysQueries.Attribute)=5));

B

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie
Johnson
Sent: Friday, 25 July 2003 10:26 PM
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/8d40a434/attachment-0001.html>