Renaming linked tables using VBA?

[kayek]

I am using Access 2000 linked to many SQL Server Tables and Views. We have a couple of different people using our database in a couple of different locations. The ODBC link is a little different for each of them. When I make a change to my database I send them a new copy. They need to re-link the tables using their ODBC link. This seems to work fine but when they re-link the tables they also have to rename the tables. The renaming of the tables is tedious and I don’t like having my users do this manually.

Form example, my users do the following to re-link the tables.
1. File – Get External Data – Link Tables.
2. Select Files of type ODBC Databases()
3. Select File Data Source
4. Select all the tables
5. Select Unique record identifier

Then they have to rename each of the tables/views. For example,

Rename dbo_tblMain1 to tblMain1.

The worst part of the process is renaming the tables/views. We have over 100 tables/views that have to be renamed. All my Access queries and forms are already setup for the name without dbo_ so they have renamed.

Is there some way I can use VBA to rename tables.

 

[PHV]

Take a look here:

http://www.peterssoftware.com/tlk.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244