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?
kmslogicConnect With a Mentor Commented:
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.
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?

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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)Connect With a Mentor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.