First of all, are there relationships set up in the Relationship Window?
Hopefully there are. This will give you a map to see how the tables are
related.
If there is not, you will have to infer these relationships based on the
queries. As you discover table relationships, you should create them in the
Relationships Window. If they're not there already, it is doubtful that you
will be able to turn Referential Integrity ON, but at the very least, you
will begin documenting the relationships.
Next, I would start with the reports. Open a report and see which query
uses it. Then open that query, see what it is based on and so forth. When
I have done this, I've used a separate piece of paper for each report and
query tree. Then I will actually draw out the tree structure. I'll also
create a list of all the queries and underneath each, list the queries or
objects (form or reports) that it directly applies to. This can help if a
single query is a base for many others. You may be able to find a tool to
do this for you, but the discovery process is very educational.
Do the same with the forms.
Next, look through the Code Modules (including the code behind forms and
reports). Often code is used to create queries or uses saved queries to
create recordsets. If DAO is used, it might look something like this:
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
If a query is just being USED in code (not created in code) it might look
like this:
Set qdf = db.QueryDefs("qryMyQuery")
or
Set rs= db.OpenRecordset("qryMyQuery")
or in ADO
rs.Open "qryMyQuery', cnn, adOpenKeyset, adLockOptimistic
There are a lot of variations on this. However, the important thing is that
the query (or table for that matter) is being used by the code. Make a note
of that as well. You can use the Find and Replace feature to find each
query name. You can set it to search through all of the code modules.
Once you've done all the reports, forms, and code, see which queries are not
accounted for. Chances are they are not used anywhere. Many mature
database projects have unused objects lying around. DO NOT delete these.
Instead, I favor renaming them with an XXX prefix, which tells me this is OK
to delete at some future time. Sometimes I will also add the data to the
name so I know how long it's been since I "deleted" it. If you run into a
problem, you can always name it back.
Lastly, I would try to make some kind of method out of all of this madness.
Try to develop some sort of rational naming structure for your queries.
There is no single correct way to do this. Often if a series of queries is
used ONLY by one report, I will name them after the report and indicate
which level they are in. However, for queries that are used as a base for
multiple other queries, this doesn't work so well. I've never come up with
anything that worked in all cases. And it doesn't matter. Just try the
best you can to do a rational job of renaming them. CAUTION: DO THIS ON
PAPER FIRST!
Once you have renamed your queries on paper in some rational manner, get
your hands on a renaming tool. Rick Fisher has a good shareware add-in
called "Find and Replace". You can find it here:
http://www.rickworld.com/download.html. (It is worth registering for the
extra features, though). This product (and others like Speed Ferret) allow
you to rename an object throughout the entire database including forms,
queries, reports, macros, and code.
By the time you get done with all this, you will know this database like the
back of your hand.
Good Luck.
