<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META content="MSHTML 5.50.4923.2500" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=494050313-25072003><FONT face=Arial color=#0000ff size=2>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...</FONT></SPAN></DIV>
<DIV><SPAN class=494050313-25072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=494050313-25072003><FONT face=Arial color=#0000ff size=2>Option
Compare Database<BR>Option Explicit</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=494050313-25072003><FONT face=Arial color=#0000ff size=2>Sub
findtbls()<BR>FindAllQueriesThatContainASpecifiedTable
("MyTableNameHere")<BR>End Sub<BR> <BR>Public Function
FindAllQueriesThatContainASpecifiedTable(tblName As
String)<BR> Dim
DB As
Database<BR> Dim rsQueries As
Recordset<BR> Dim sqlStr As
String<BR> Dim index As
Integer<BR> <BR> Set DB =
CurrentDb()<BR> <BR>
sqlStr = "SELECT MSysObjects.Name AS QueryName " &
_<BR>
"FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId " &
_<BR>
"WHERE (((MSysQueries.Name1)=""" & tblName & """) AND
((MSysQueries.Attribute)=5));"<BR>
<BR> Set rsQueries =
DB.OpenRecordset(sqlStr)<BR> rsQueries.MoveLast:
rsQueries.MoveFirst<BR> <BR> 'print out the
resuls of our query<BR> Debug.Print "------ Queries containing
table '" & tblName & "' -------"<BR> Debug.Print
"------ number of queries : " & rsQueries.RecordCount<BR>
For index = 1 To rsQueries.RecordCount Step
1<BR> Debug.Print
rsQueries!QueryName<BR>
rsQueries.MoveNext<BR> Next<BR>
<BR> rsQueries.Close<BR> Set DB =
Nothing<BR>End Function<BR></FONT></SPAN></DIV>
<DIV><SPAN class=494050313-25072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=494050313-25072003><FONT face=Arial color=#0000ff
size=2>Scott</DIV></FONT></SPAN>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Lonnie Johnson
[mailto:prodevmg@yahoo.com]<BR><B>Sent:</B> Friday, July 25, 2003 8:26
AM<BR><B>To:</B> Access Developers discussion and problem
solving<BR><B>Subject:</B> [AccessD] (Tip) Locate a Table Name within
Queries<BR><BR></FONT></DIV>
<P>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.<BR><BR><B><FONT
color=green><BR>Option Compare Database<BR>Option Explicit<BR><BR>Sub
findtbls()<BR>FindAllQueriesThatContainASpecifiedTable
("MyTableNameHere")<BR>End Sub<BR> <BR>Public Function
FindAllQueriesThatContainASpecifiedTable(tblName As
String)<BR> Dim
qd As
QueryDef<BR> Dim
DB As
Database<BR> Dim
tb As
TableDef<BR> Dim rsQueries As
Recordset, RS As
Recordset<BR> Dim
sqlStr As
String<BR> Dim
index As
Integer<BR> Set DB =
CurrentDb()<BR> Set tb =
DB.CreateTableDef("tempTblDef") 'create
temp table<BR> sqlStr = "SELECT QueryName FROM " &
tb.name & " WHERE ((([QuerySQL]) LIKE '*" & tblName &
"*'))"<BR> <BR> tb.Fields.Append
tb.CreateField("QueryName", dbText) 'add fields we
need<BR> tb.Fields.Append tb.CreateField("QuerySQL",
dbMemo)<BR> DB.TableDefs.Append
tb<BR> <BR> Set RS =
tb.OpenRecordset() 'open
the table!<BR> <BR> For Each qd
In
DB.QueryDefs 'get
sql and name of each query,
ignoring<BR> If (Left(qd.name,
1) <> "~")
Then 'system
and hidden
queries<BR> RS.AddNew<BR> RS!QueryName
=
qd.name<BR> RS!QuerySQL
=
qd.SQL<BR> Debug.Print
RS!QueryName<BR> RS.Update<BR> End
If<BR> Next<BR> RS.close<BR> <BR> 'open
up a recordset based on the temp table using a SQL
query<BR> Set rsQueries =
DB.OpenRecordset(sqlStr)<BR> rsQueries.MoveLast:
rsQueries.MoveFirst<BR> <BR> 'print
out the resuls of our query<BR> Debug.Print "------
Queries containing table '" & tblName & "'
-------"<BR> Debug.Print "------ number of queries : "
& rsQueries.RecordCount<BR> For index = 1 To
rsQueries.RecordCount Step
1<BR> Debug.Print
rsQueries!QueryName<BR> rsQueries.MoveNext<BR> Next<BR> <BR> rsQueries.close 'get
rid of it!<BR> DB.TableDefs.Delete
tb.name 'delete
temp. tabledef<BR> Set DB = Nothing<BR>End
Function<BR></FONT></B></P><BR><BR>
<DIV>
<DIV>
<P align=center><STRONG>Lonnie Johnson<BR></STRONG><STRONG>ProDev</STRONG>,
Professional Development of MS Access Databases<BR>Visit me at ==> <A
href="http://www.prodev.us/">http://www.prodev.us</A></P>
<P><BR><A
href="http://www.galaxymall.com/software/PRODEV"><BR></A><BR> </P></DIV></DIV>
<P>
<HR SIZE=1>
Do you Yahoo!?<BR><A
href="http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com">Yahoo!
SiteBuilder</A> - Free, easy-to-use web site design
software</BLOCKQUOTE></BODY></HTML>