Need to Rename Table Name

[Number11]

Please could someone help here, i need to change a current table within the backend of a split database but will all of te queries linked to the current table get updated or will i have to updated every query?

 

[gemma-the-husky]

You will have to update every query.


See the code I posted in this thread to search all the querydefs for specific text

Object dependencies using VBA | Access World Forums (access-programmers.co.uk)

if you search for your table name, you can simply replace every occurrence of the old table name with the new table name. You don't have to explicitly save the query. Simply this does the job. DAO is working directly on the SQL string which is actually the query.

qry,SQL = replace(qry.SQL, "old table name", "new table name")

note that if you have a space in the new table name and didn't before, you need square brackets [] around the new table name.

Copy your database before you do this, in case something goes wrong!

 

[Pat Hartman]

Changing all the queries/code/RecordSources in the FE keeps everything clean. It can be a lot of work so you shouldn't do it unless you really need to. However, there are two ways to avoid a big change in the FE. Option 1 obfuscates the name change so I don't recommend it since it will almost certainly confuse people. Option 2 is better but actually cleaning up everything is the best solution.
1. If you changed the name of the table but not any of the columns, you can delete the current link and link to the new table name but give the linked table the same name as the original. This is not obvious when you look at the table list though so I don't recommend it.
2. If you changed the name of the table AND/OR the names of some columns. Again delete the original link and link to the new table. Create a querydef that selects the new linked table and alias any column names that have changed. Save the querydef as the name of the original linked table. So, if you ever see a querydef named tblSomeName, you KNOW this is what someone did.

 

[isladogs]

Unlike Pat, I do recommend renaming the BE table then creating an alias which has the original table name. Doing that means all your queries and code will continue to work perfectly. Just make sure you make a backup and have Name Autocorrect switched off before you do this.

Whilst it may seem that just turning on Name Autocorrect' will allow the name change to 'percolate through' it will almost certainly fail to update certain queries e.g crosstab IIRC...and it doesn't make any changes to code.