Complicated Loop

[auujxa2 via AccessMonster.com]

Someone who recently left my company built a database that runs 1 macro.
The macro consists of many departments, for which each department has 4
queries, and 4 tables. (i.e. Dept321-tbl, Dept321-qry, etc) per department.

I would like to rename all the tables/queries with all new departments. I
was thinking first to create a table, first field being the current
departments, the second field being the new corresponding deptartment.

Then, run a loop to replace the table naming conventions from the old # to
the new #.

My question is, is it possible to do a find and replace within all the
queries? (i.e. maybe in SQL view?)

And guidance you can give would be most helpful. I didn't build the database
originally, so it's the hand I'm dealt. I would have created the query in vb
using strSQL and run a loop instead of building over 100 tables/queries

thank you in advance!

ps- i know if you rename the tables first, the table names in the queries
will change accordingly, but I also need to change the criteria

 

[Jerry Whittle]

1. Don't do it. From what you describe, the database isn't built properly and
needs to be redesigned and rebuilt from scratch.

2. Don't do it Part II. Chances are that something will go wrong and the
database won't work right. At the least make backups first.

3. Do it but don't reinvent the wheel. Rich Fisher's excellent Find and
Replace add-in might be just the ticket. If you register it (one of the best
$37 I've ever spent), the cross-reference report can find what queries use
which reports and forms. It's a good way to find orphans.
http://www.rickworld.com/

4. You said:

ps- i know if you rename the tables first, the table names in the queries
will change accordingly, but I also need to change the criteria

If you are depending on NameAutocorrect, you may be very disappointed.