Solved

Determine which queries/forms use a specified table

Posted on 2006-07-12
8
239 Views
Last Modified: 2012-08-14
How can I determine which queries/forms within an Access database use a specific  table?
0
Comment
Question by:sfmoore2006
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17095657
Which version of Access?
0
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 50 total points
ID: 17095687
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 17095738
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:sfmoore2006
ID: 17095810

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
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 20 total points
ID: 17095826
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 55 total points
ID: 17096405
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 17098150
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
 

Author Comment

by:sfmoore2006
ID: 17104978

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question