Can you search a table name in queries?

[ManningFan]

Let's see if I can ask this without confusing you or myself.

I need to be able to type a tablename into a box, and search my entire
database to see which queries are using that table.

Is this even possible?

[RoyVidar]

"ManningFan" <ma********@gmail.comwrote in message
<11**********************@74g2000cwt.googlegroups. com>:

Let's see if I can ask this without confusing you or myself.

I need to be able to type a tablename into a box, and search my
entire database to see which queries are using that table.

Is this even possible?

I would think you could loop the querydefs collection, then investigate
the SQL property of each of them using for instance the InStr function.
air code

sub testing(byval v_strTable as string)

dim qd as dao.querydef
dim db as dao.database

set db = dbengine(0)(0)
for each qd in db.querydefs
if instr(1, qd.sql, v_strTable, vbtextcompare) 0 then
debug.print "found in " & qd.name
end if
next qd
set qd = nothing
set db = nothing

end sub

A search in this NG would probably find a plethora of more advanced
routines to do something like that, including checking for the
tablename
in dynamic sql/code, rowsources, recordsources ...

--
Roy-Vidar

[Allen Browne]

See if this query helps:

SELECT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects
ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

There are tools that help trace dependencies. Here's a freebie:
Renaming fields and tracing dependencies a utility by Chas Dillon
at:
http://allenbrowne.com/ser-41.html

Here's a commercial one:
http://www.speedferret.com/

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ManningFan" <ma********@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

Let's see if I can ask this without confusing you or myself.

I need to be able to type a tablename into a box, and search my entire
database to see which queries are using that table.

Is this even possible?

[ManningFan]

I ended up using some code I found on
http://www.dbforums.com/t677218.html but I had to modify it a bit. I
combined it with code found on
http://www.mvps.org/access/queries/qry0002.htm to produce a form that
gives people entirely too much information. But hey, that's what they
wanted so as long as they're happy...

Allen Browne wrote:

See if this query helps:

SELECT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects
ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

There are tools that help trace dependencies. Here's a freebie:
Renaming fields and tracing dependencies a utility by Chas Dillon
at:
http://allenbrowne.com/ser-41.html

Here's a commercial one:
http://www.speedferret.com/

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ManningFan" <ma********@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

Let's see if I can ask this without confusing you or myself.

I need to be able to type a tablename into a box, and search my entire
database to see which queries are using that table.

Is this even possible?

[Smartin]

Allen Browne wrote:

See if this query helps:

SELECT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects
ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

There are tools that help trace dependencies. Here's a freebie:
Renaming fields and tracing dependencies a utility by Chas Dillon
at:
http://allenbrowne.com/ser-41.html

Here's a commercial one:
http://www.speedferret.com/

Good stuff. My compliments to the chef(s)!

--
Smartin