Determine which queries/forms use a specified table

How can I determine which queries/forms within an Access database use a specific  table?
sfmoore2006Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Hi sfmoore2006,

Rick Fisher's 'Find & Replace utility' (http://www.rickworld.com, free 30-day trial, $39 buy) is an Access add-in that allows you to find and replace strings in your Access database, and can do this in all areas:  Tables, table definitions, queries, query definitions, forms, form controls, reports, report controls, database properties, and all VBA code.  

This way, you can enter a query/form name, hit Search, and find/replace everywhere in your app its used.

I've been using this for ten years now and love it.

Hope this helps.
-Jim
0
 
Leigh PurvisDatabase DeveloperCommented:
Which version of Access?
0
 
Raynard7Connect With a Mentor Commented:
For queries it is not too hard.  The idea is that you can cycle through all the queries using VBA and ask for the SQL contained within and then say is this sql like the table name.

this is done at

http://www.experts-exchange.com/Databases/MS_Access/Q_21181895.html

where they obtain a list of all the queries (querydefs) and then extract their name (substitute with .sql) and then say if q.sql like "*" & tablename & "*" then debug.print q.name

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
dannywarehamCommented:
What's the actual purpose of this?
Do you need to rename objects?

There are loads of tools out there to rename fields/tables/forms/variables and all sorts.
0
 
sfmoore2006Author Commented:

Thanks for all the quick responses.  The Access version is 2000.  The VBA solution for finding which queries use a given table will be a big help!  The purpose is not to rename tables; its basically to help us perform maintenance and development on an existing database which contains numerous queries, tables, and forms developed over time by various people without very much documentation.
0
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
Yeah - then either a third party tool - or iterating through the collections of other objects looking for your tables is the only way.

(Access 2003 has a dependency object added to it's OM now which is quite good at this - and much faster - not requiring opening of all the objects.  That's the reason I asked about version :-)
0
 
dannywarehamCommented:
You should check out www.ssw.au.com

They provide a renaming tool - and they also do a code documentor tool, too.

Very reasonable.
0
 
sfmoore2006Author Commented:

I believe I've closed this one out.  Thanks to all for taking the time to help.  In addition to the VBA solution, I'll be following up on the 'Find and Replace' utility per jimhorn.  Ten years of satisfaction with the product is pretty high praise!

FYI, I was never able to reach www.ssw.au.com--perhaps the site is no longer valid or is having problems.
0
All Courses

From novice to tech pro — start learning today.