<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=840182813-25072003><FONT face=Arial color=#0000ff size=2>Here's
a sliightly shorter way to do it directly in a parameterised
query:</FONT></SPAN></DIV>
<DIV><SPAN class=840182813-25072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=840182813-25072003><FONT face=Arial color=#0000ff
size=2>PARAMETERS [Enter table name] Text ( 255 );<BR>SELECT MSysObjects.Name,
IIf(IsNull([Expression]),[Name1],[Name2] & "(" & [Expression] & ")")
AS SourceTable<BR>FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId<BR>WHERE (((IIf(IsNull([Expression]),[Name1],[Name2] &
"(" & [Expression] & ")"))=[Enter table name]) AND
((MSysObjects.Type)=5) AND ((MSysQueries.Attribute)=5));<BR></FONT></SPAN></DIV>
<DIV><SPAN class=840182813-25072003><FONT face=Arial color=#0000ff
size=2>B</DIV></FONT></SPAN>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>On Behalf Of </B>Lonnie
Johnson<BR><B>Sent:</B> Friday, 25 July 2003 10:26 PM<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>