Establishing when a table is referenced

[aine_canby]

Hi there,

How can I find out where a table is referenced in a form, query, or in
visual basic code, within an access 97 project. I've just inherited an
access database with a lot of problems in it and it would make my life
easier if I could find out when a table is being referenced.

Thanks,

Aine.

 

[Guest]

Click on TOOLS - Analyze - Documenter.
Select all forms, queries, and reports. Pick your options and click OK.
Click OK and the click on the icon that is a blue 'W' to save as a RTF
document and search using Word.

 

[George Nicholson]

Access Analyzer http://fmsinc.com/

Pretty sure there is a 97 version available. It won't be free, but you'd
have to judge it's value vs the amount of time it saves you.
(I have no connection to FMS other than as a customer).

It can create a x ref list of all object references within your db including
Tables/queries/Fields referenced by queries/forms/reports.It will also x
reference object references within code *if* it can resolve the reference:

Code examples that would include MyTable.. in a Xref report:
Const strTableName = "MyTable"
Set rs = db.OpenRecordset ("MyTable2")
DoCmd.OpenTable "MyTable3"

It would not be able to resolve the object references in the following
(reference within a variable):
strSQL = "SELECT * FROM MyTable"
DoCmd.OpenTable "MyTable" & i
DoCmd.OpenTable strTableName

If these limitations seem onerous, consider that adopting a coding style
similar to the following would allow the tool to find all object references
within code:
Set tdf = db.TableDefs("MyTable")
strSQL = "SELECT * FROM " tdf.Name

HTH,

 

[John W. Vinson]

Hi there,

How can I find out where a table is referenced in a form, query, or in
visual basic code, within an access 97 project. I've just inherited an
access database with a lot of problems in it and it would make my life
easier if I could find out when a table is being referenced.

The Documenter is one tool; there are a number of third-party tools to help
with this task as well.

Free: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson [MVP]