asked on

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?

Thanks,
Davy

ASKER CERTIFIED SOLUTION

kmslogic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

Davy,

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.

Jim.

Davy2270

ASKER

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?

Davy

Davy2270

ASKER

Hi Jim,

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

Davy

Davy2270

ASKER

FYI

I'm leaving the office now (Belgium), will be back in 15 hours.

Jim Dettman (EE MVE)

<<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

Jim.
Relink2.Zip

Davy2270

ASKER

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.
example:
ODBC;DSN=EDC01;;TABLE=NKEWPRDTA_INRHLD01
needs to change to
ODBC;DSN=EDC01Htest;;TABLE=HEEUAWDTA_INRHLD01

Davy2270

ASKER

here's an example of a connect
? CurrentDB().tabledefs("NKEWPRDTA_INRHLD01").Connect
ODBC;DSN=EDC01;

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Davy2270

ASKER

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.