Need to change table name throughout MDB

[dtm418]

I have to change a table name due to a new county being added to that table. There are many queries and reports that use that table. Is there a way to change everything at once? I tried V-Tools but it will not install.

 

[jdraw]

There are conditions, such as building queries/sql within vba, that are not easily found.
Vtools has been recommended in the past, as has a Find and Replace routine (name escapes me at the moment Rick Fisher I think).

If you really have to change a table because of a new record being added (new county), it seems you have found a basic design flaw.

Can you tell us more about your table, and your application and what exactly is causing the need to change the table name?

 

[speakers_86]

Here is Rick Fisher's tool. It works wonders.

Buy why does adding a record make you need to change the name of the table? Also, the name of the table really shouldn't matter.

As for installing v-tools, you may need to run as admin. Go into your programs folder, find the Access program under Office, right click, Run as Admin. Then try installing V-tools.

 

[dtm418]

The Rick Fisher tool worked great! Thank You. I followed all those instructions on V-Tools with no luck. Why do I need to rename? In my organization we have tables for groups of counties within the state. It is just much easier to work withing a group of three counties when looking for information based on all those people. No query has to be run. A simple rick-click and filter is sometimes needed. This way we don't have to run a query every time to get the three or four counties in a group first to look at the data. Sure we could work from a query all the time but then we loose the cool features of quickly adding a column for temporary use. There are no users per say. Just the administrator using the MDB at the time.

And just recently a county was moved from one group to another. Hence the necessary name change to a table. (Could we use group names instead? No. They change real often: numbers, then colors, then long names. According to some new manager's whim.)

 

[Pat Hartman]

This is reflective of spreadsheet thinking. In a relational database, you would never have tables that constructed like this. Instead, you would have a single table with an additional data field. That field would serve to collect the counties into groups. if a county moves groups, it is simply a matter of changeing the value in its group column. You wouldn't need to change anything else. The other advantage of a properly normalized schema is that your queries are simpler and there are fewer of them. Instead of having a different query for each table and all the overhead caused by that, you have one query that either prompts for a group or refers to a combo box on a form where you have selected the group you want. I'm sure fixing this problem will also allow you to eliminate forms and reports as well as queries.

So, since you need to make a big change now, make it the right change and it will be the last time you have to deal with this problem.