Changing Query Names and Table Names

[ibeetb]

1)If I change a name for a table, will I break all queries that run against
this table?
2)If I cange a query name, will I screw up other queries that use the query
whose name I am changing?
If true to the above, if there a quick clean way to update all dependent
queries and tables without having to modify each one one-by one?

 

[John Vinson]

1)If I change a name for a table, will I break all queries that run against
this table?

Yes... probably. See below.

2)If I cange a query name, will I screw up other queries that use the query
whose name I am changing?

Yes... probably. See below.

If true to the above, if there a quick clean way to update all dependent
queries and tables without having to modify each one one-by one?

Depends on your version of Access. In A2002 and later, there's a "Name
Autocorrect" feature available on the Tools... Options menu, General
tab. Most developers uncheck it for routine use because it can cause
MAJOR performance problems, and it's been accused of contributing to
database corruption; but you can back up your database, check the
"track name autocorrect" checkboxes as appropriate, and go ahead and
change the name.

If you have an older version, you'll need a third party tool to do
this. Some options:

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]

 

[Guest]

There is an option called Name AutoCorrect (Tools -> Options -> General).
When this is turned on, and you rename a query or table, it finds the
references to it and changes the name there also. There are some limitations,
however.

First and foremost, Name AutoCorrect can slow everything down significantly
during development because it is constantly checking for cross-references, so
you would want to run it, do the changes, and then turn it off again.

Secondly, it does not catch every reference. It will catch references in the
FROM portion of your query, but will not catch those inside the domain
portion of domain aggregate functions used within your query. For instance,
in this domain aggregate statement used to generate one output field in a
query", the "Query1" reference is updated automatically with Name
AutoCorrect, but "CustomerNotes" is not :

NotesCount: DCount("[Notes]","[CustomerNotes]","[CustomerID] = " &
Query1!CustomerID)

After renaming Query1 to Query2 and CustomerNotes to CustomerNotes2, the
statement looks like this:

NotesCount: DCount("[Notes]","[CustomerNotes]","[CustomerID] = " &
Query2!CustomerID)

It will also not find references to the query in VBA, since these are
generally presented as strings, and not as objects (e.g. in DoCmd.OpenQuery
"Query1", then Query1 will not be updated to Query2 by Name AutoCorrect).

So... if you have no domain aggregate functions in your queries, you could
turn on Name AutoCorrect, rename the tables/queries, then turn it off and do
a search-and-replace through your VBA code.

If you do have domain aggregate functions in your queries, you would
probably be better off to do a search-and-replace of both your VBA code and
the SQL of your queries using the QueryDefs collection. Re-post if you want
more details; it's a little more complex.