We help IT Professionals succeed at work.

Change System and Library name of a linked table

Davy2270
Davy2270 asked
on
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
Comment
Watch Question

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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.

Author

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?

Davy

Author

Commented:
Hi Jim,

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

Davy

Author

Commented:
FYI

I'm leaving the office now (Belgium), will be back in 15 hours.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

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

Author

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

Author

Commented:
here's an example of a connect
? CurrentDB().tabledefs("NKEWPRDTA_INRHLD01").Connect
ODBC;DSN=EDC01;
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Davy,

  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:

ReattachODBCTables("EDC01Htest")

  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.

Jim.


Reattach-Davy.mdb

Author

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.