By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,589 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Can you search a table name in queries?

P: n/a
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?

Aug 24 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"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
Aug 24 '06 #2

P: n/a
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?

Aug 24 '06 #3

P: n/a
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?
Aug 24 '06 #4

P: n/a
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
Aug 25 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.