Change System and Library name of a linked table

I have some Access databases that have linked tables to an AS400 machine. We will have to do some testing in the near future on the same tables, however they will be on a different System and a different library.

I tried to update the linked table via Linked Table Manager. I prompted for a new location and pointed the new System. This did not work. Obviously because the Library name did change.

Is there a way I can change the System (DSN) and the Library name of a linked table?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can set the library in your AS/400 DSN, so when you point the linked table manager to your new ODBC data source make sure you've specified the default library on that data new data source.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  You can use the linked table manager, just be sure to check the box at the bottom "Always prompt for a new location".

  Also, if you have tables from multiple sources, make sure you do each source seprately (ie. 10 tables belong to source A and 15 to B, do the A tables first, then the B tables).

  If you don't, you'll get prompted once for each table rather then just once for the group.

  Finaly, if you want a more automatic way of handling this, you can control table linking in code by modifying the linked tables .Connect property.  

  I have code here to handle quite a few different situation, including changing DSN's.

Davy2270Author Commented:
Hi kmslogic,

I have followed your instructions, but it still does not link to the new location. Also the library description in the Linked table name (navigation pane) does not change.

Can we perhaps do this in VBA?

The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Davy2270Author Commented:
Hi Jim,

I am very interested in your code. Can you share?

Davy2270Author Commented:

I'm leaving the office now (Belgium), will be back in 15 hours.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I am very interested in your code. Can you share?>>

  Sure.  Attached is some re-linking code.  There are two techniques in the sample DB:

1. Refreshing existing links.
2. Recreating new links from a table.

  Now sure which will best fit your needs.  I also have code to change the server a DSN is pointing to, but I'm not sure if you need that or not.

  If you want to post an example of one of your connects that would be helpful.  You can get that by going into a code module, keying ctrl/G, then type:

? CurrentDB().tabledefs("<name of table>").Connect

Davy2270Author Commented:
Jim, I think you have the solution at hand. Could you tweak the code for me or write some?
In my databases I only want the linked tables from DSN EDC01 and Library NKEWPRDTA to be changed.
needs to change to

Davy2270Author Commented:
here's an example of a connect
? CurrentDB().tabledefs("NKEWPRDTA_INRHLD01").Connect
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  OK, hope you were not waiting on this.  Morning quickly streched into afternoon here.

  I thought that original DB had the ODBC routines in it, which is why I was a bit puzzled at your intial response, but know I now why.

   Attached is another DB.  I've added to this a module for ODBC with two routines:

1. ReattachODBCTables
2. FixServerName

  FixServerName is just a down and dirty procedure to substitue one string for another in the connect properties.  Use that if you want to do something quick.

ReattachODBCTables() is a much cleaner setup and it's table driven.  Of course there are a number of ways to do this, but for this setup, enter one record in the table for each of the local tables and the possible DSN/Library it could use.

  Then call the rountine with the DSN:


  For each table entry it finds with that DSN, it will either create a link for it or refresh the table with the existing link.

  Sorry again for taking so long with this.


Davy2270Author Commented:
I divided the points between kmslogic and Jim.
kmslogic was right in seting the default Library, Jim provided me the code for changing the sources.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.