Is there a way to see the links between all tables, queries, reports?

[Rebel]

I was recently doing a little Spring Cleaning in my database and deleting old tables and queries that were no longer used or had been for experimental purposes. Unfortunately, I unknowingly deleted a query that was being used by a report I still needed and it took me several hours to find and correct the problem.

Not wanting to repeat this mistake, I began searching for a way to display my reports, queries, and tables and all the objects each is linked to. I thought it would be nice, for instance, to have a hard copy list of all my queries that showed where they are derived from and what other objects reference them. Is there a way to do this?

I've tried using the "Relationships" button and working with the Documenter, but I haven't found what I'm looking for. I have also tried a Google search, but, admittedly, I may not be using the proper terminology to get good results.

Thanks for your time.

 

[Crilen007]

Somewhat possible, but not really.

It wont pick up any VBA links, among other things. I tried this.

Best way is to have documented the database from the beginning.

Or take the time to document it.

 

[The_Doc_Man]

The closest I ever came to this took days to write though it was able to find most VBA references to data entities. But then, I'm known for being hard-core about some things. You have to have a text-parsing tool to do it right, and it had better be a darned good tool.

Basically, you can build a table into which you place all references. Two parts... referencer and referenced item. Plus I had some extra attributes like object type and owner and some other stuff. So ...

You find each collection in your database. Tabledefs, QueryDefs, Modules, and Documents. (A document is either a form or a report for the purposes of that collection.) Note that linked tables might look a bit strange because they don't hold all of their own definitions - just enough to get by from the Access side of the linkage.

You do a For Each loop on the collection. For each member of the collection, you have to know what sub-collections it contains. E.g. A single TableDef contains a Fields collection and an Indexes Collection (I think). An index can contain a Fields collection. So you enumerate the properties and fields and such into the references table. Thus-and-such field is a child of the xyz table. Thus-and-such a field is a member of the xy index of the xyz table.

For QueryDefs, each query has a recordsource and a FieldDefs collection. (Yeah, it usually also has an SQL string... go figure). The recordsource is either a table or another query. Note that some queries begin with the ~ character. These are "generated" queries implied by a particular report for which you used the wizard to define a particular sort order and grouping.

You have to open a document to know what it contains, and once you do, you can access its form/report properties - including recordsource - and its Controls collection. The individual controls will have a controlsource plus other properties. Don't forget to close what you open.

For modules, each module is a collection of Lines (of source code) which is where the text parser comes into play. This is why it takes days to do it right. If not longer. In each module, you watch for a Sub or Function header and an End Sub or End Function trailer. Anything in between is the body of the function. Don't forget to look at any arguments of the Sub/Function if you do that. This is also where you REALLY find out why you don't want embedded spaces in object names.

If you are not comfortable doing text parsing, you need to Google-Search for a cross-referencer product for Access. In my case, I did it for the mental exercise but I cannot sell the product. First, it is incomplete. Second, it is imperfect. Third, it is for an old version of Access. Fourth, I don't own it. My employer does, and THEY aren't selling either.

 

[Rebel]

That's what I was afraid of. I was hoping that there was some sort of built-in function in Access that would do it. Luckily, my database is small enough that, while a bit time-consuming, it won't be too terrible to go through each individual Table/Query/Report to find what links to what.

Thanks for the replies.

 

[modest]

Forgive me if this has been said, but I haven't had the time to read through the entirety of this thread. When you right-click on an object such as a Report, select "Object Dependencies" from the menu and Access should return what you are looking for.