renaming and deleting tables

[Amity]

I found this quote in another help thread;

Queries, Forms, and Reports all use Tables. If you rename a table, you will
break anything that depends upon it. There is a name auto-correct feature,
but it doesn't work in every case.

I am updating and 'cleaning up' some databases with heaps of tables and
heaps of queries. A lot of the tables and queries are not needed any more,
or were created temporarily and have been forgotten to be deleted. Is there
an easy way I can look up if a certain table is being 'depended' on in a
querie or several queries? If so I can easily make sure all the places it is
used are updated if I change the table name, or deleted if the table is
deleted.
It is very time consuming going through each querie and writing down which
tables it 'depends' on for data, is there an easier way?

 

[Brendan Reynolds]

I found this quote in another help thread;

Queries, Forms, and Reports all use Tables. If you rename a table, you
will
break anything that depends upon it. There is a name auto-correct feature,
but it doesn't work in every case.

I am updating and 'cleaning up' some databases with heaps of tables and
heaps of queries. A lot of the tables and queries are not needed any
more,
or were created temporarily and have been forgotten to be deleted. Is
there
an easy way I can look up if a certain table is being 'depended' on in a
querie or several queries? If so I can easily make sure all the places it
is
used are updated if I change the table name, or deleted if the table is
deleted.
It is very time consuming going through each querie and writing down which
tables it 'depends' on for data, is there an easier way?

I don't think there is any 100% foolproof method. For example it is possible
to have code such as this in the Open method of a form ...

Const strcQueryName = "qryExample"
If SomeCondition = True Then
Me.RecordSource = strcQueryName & "ByName"
Else
Me.RecordSource = strcQueryName & "ByNumber"
End If

I don't think there is any tool that will reliably detect that this form is
dependant on both qryExampleByName and qryExampleByNumber.

With that caveat, there are some tools that can help ...

Find and Replace
www.rickworld.com

Total Access Analyzer
www.fmsinc.com

SPEED Ferret
www.moshannon.com

 

[George Nicholson]

FYI:
I know Access Analyzer (and maybe the others, but I haven't used those)
would be able to include the query object cross ref in its documentation
*IF* (and I know that's a huge if) your sample code were modified to
something like:

Dim db as dao.database
Dim qdf as dao.QueryDef

Set db = currentdb
If SomeCondition = True Then
set qdf = db.querydefs("qryExampleByName")
Else
set qdf = db.querydefs("qryExampleByNumber")
End If
Me.RecordSource = qdf.name

Set qdf = nothing
set db = nothing

It might be a pain to retrofit an existing db like this (although if naming
conventions were in use from the start then its easy to search code for
"qry*", etc.). But once you know to do it, it is a fairly easy habit to get
into and elevates the tool output to "client deliverable technical
documentation" quality, and that makes it very much worth the effort (IMHO).

HTH,

 

[Brendan Reynolds]

Thanks George, but the code was intended only as an example to demonstrate
the point that objects may be referenced in ways that may make it difficult
or impossible for automated tools to detect the dependency. You are correct
of course that there are things that a developer can do to make his/her
application more accessible to automated tools. But often we need to use
these tools to examine applications that were developed by someone else, and
can't assume that they were developed with automated tools in mind. I guess
the point that I'm trying to make is that unless you are very sure that the
app was carefully developed with automated tools in mind (and perhaps even
if you think it was) it is not safe to assume that no detected dependency
equals no actual dependency.

The tools can help, but it is very important to have reliable backups before
deleting anything. My own practice in this situation is to make a back up,
delete just one believed-to-be-redundant object, test thoroughly, and then
make another back-up before moving on to the next object, not forgetting to
document exactly what was removed from each back-up copy.