Tracking tables and queries

[RichardMGreen]

Hi all

I'm fairly certain there's a way to track which tables are used by which queries and which queries lead to others (if you see what I mean) but I can't remember what it is.

Can someone help me out?

 

[SydneyGeek]

Search your Help for Object Dependencies. How you get to it changes depending on the version; it's available in 2003 up.
Note: you may need to turn on a couple of options to make it work; Name Autocorrect is one such option in 2003. BUT... turn it back off once you have done your probing. It's one option that can slow your application down, and possibly cause corruption.

Denis

 

[RichardMGreen]

Thanks for that.
I'm using Access 2003 so I'll look through the help files and see what I can find.

::edit::
I've just checked the Object Dependencies on the View menu but it looks like it only works on the object selected.
Is there a way of doing it for the entire database including all the queries (like a database map)?

::edit 2::
I've just seen a Documenter under the Tools/Analyze menu. Is that what I need (can't run it as it's not installed)?

 

[Derek Brown]

There is a 'Database Documenter' in one of the tools menus (I am using Access 2010 so cannot remember where is was in 2003).
It may give you what you want.

 

[RichardMGreen]

Thanks for that. Looks like a call to IT to get it installed then.

 

[RichardMGreen]

On a related but slightly different question, I have a few queries that run after an import routine which show me any extras I need to put into master tables.

Is there any way of stopping the queries from showing results if the result is nothing?

At the moment I'm just using DoCmd.Openquery in Access VBA, e.g.
DoCmd.OpenQuery "qry_New_Referral_Source_List", acViewNormal

 

[boblarson]

You could do a DCount to see:

If DCount("*", "qry_New_Referral_Source_List") > 0 Then
   Docmd.OpenQuery "qry_New_Referral_Source_List"
End If

 

[RichardMGreen]

Works perfectly, thanks Bob.