Is there a file where the Linked Table Manager information is stor

[Dave]

I am hoping that there is a script within Access where the Linked Table
Manager link information is stored.

I have a number of files, over 300 that I need to move and I am hoping that
I do not have to restore the link one at a time. I am hoping I can open and
edit a script and make it all happen easily.

Thanks

Dave

 

[Jerry Whittle]

There is the hidden MysObjects system table. However I don't think that you
are allowed to update it manually, and if you do and mess it up, you'll
probably trash the database. Therefore make a backup first.

Here's a little query to show the linked tables:

SELECT MSysObjects.Name, MSysObjects.Database, MSysObjects.Connect
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))
ORDER BY "Table", MSysObjects.Name;

 

[John Spencer]

The connection information is a property (connect property) of each linked table.

You can use VBA to modify the connect property of each table, but there is no
built-in functionality.

If you are moving the 300 files from one directory to another directory then
you should be able to use a VBA routine to update all the connect strings to
reflect the new path.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave]

Thanks Jerry, it is as you said. I am able to see the data, but cannot edit
same. I then searched the internet and see there are all kinds of notes
"Don't do it!"

I did back up the db. It looks so simple to run an update.

Dave

 

[Dave]

Hi John

Would I run this routine against the MSysObjects Table?

Dave

 

[Douglas J. Steele]

No, you'd loop through the DAO TableDefs collection, resetting the value of
the Connect property.

Take a look at http://www.mvps.org/access/tables/tbl0009.htm and
http://www.mvps.org/access/tables/tbl0010.htm at "The Access Web" for the
sort of thing you're in for.

 

[John Spencer]

You could use the MSysObjects table to get a list of the linked tables and
then use the names returned in a loop to update the connection string.

Or you can simply step through all the tabledefs in your database and update
those where the length of the connect property is greater than zero. Or test
the the connect property to see if it contains information the path to the
linked file and if so change the connect string to reflect the new path.

You might try getting the current connect string by using something like the
following in the immediate window.

?CurrentDB().TableDefs("NameOfLinkedTableFile").Connect

That will return the connection string and if you post that someone should be
able to suggest how you modify the connect string.

For instance here is a connect string to an Excel sheet (Link named Sheet1)
Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\Documents and Settings\spencer\My
Documents\Excel Docs\Maryland2009.xls

If I moved the the file to C:\SpecialStuff then I would use a line like

CurrentDB().TableDefs("Sheet1").Connect = "Excel
5.0;HDR=NO;IMEX=2;DATABASE=C:\SpecialStuff\Maryland2009.xls"

As a matter of convenience I might use the REPLACE Function to do this.

strOldLoc="C:\Documents and Settings\spencer\My Documents\Excel Docs\"
strNewLoc="C:\SpecialStuff\"

Then I could do something like the following in a loop through the tableDefs

With tblDef
If Instr(1,.Connect,strOldLoc)>0 then
.Connect = Replace(.Connect,strOldLoc,strNewLoc)
End if
End With

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[theDBguy]

Hi Dave,

I am new to this newsgroup so this may not be an appropriate question to
ask. If so, I apologize in advance.

But when you said you have 300 files to move, are you talking about 300
database files or 300 tables? If tables, that seems to be a large amount of
linked tables in my experience (which isn't much - I don't do any commercial
applications). If files, how are you linking to them in your Front End
database.

Just curious...

 

[Armen Stein]

I am hoping that there is a script within Access where the Linked Table
Manager link information is stored.

I have a number of files, over 300 that I need to move and I am hoping that
I do not have to restore the link one at a time. I am hoping I can open and
edit a script and make it all happen easily.

Hi Dave,

As others have said, it would be very unusual to link to over 300
Access *files* (MDBs). More likely would be 300 *tables* (but that's
a lot too.) Either way, Linked Table Manager is pretty cumbersome.

Anyway, if you want a more automated approach to relinking tables,
you're welcome to use our free J Street Access Relinker on our
J Street Downloads page: http://ow.ly/M56Q

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Dave]

We extract a lot of data for dbs on SAP, SQL & Oracle and we the developers,
create reports combining data from all of these resources. So we typically
save the data as text files, linking this data to Access dbs and create more
data. It pays the bills.

So we pass around hundreds of text files that we link to. I have 600+ linked
text files for this one app alone.

So the plan is to move all of the linked text files to an external hard
drive instead on the local client hard drive.

I can re-link the files, but I was hoping there was an easy table to just
run an update, change the path and life would be good.

I hope this answers the question.

Dave

 

[theDBguy]

Hi Dave,

Thanks for responding to my inquiry. It was just an unusual setup for me but
I guess I don't know everything.

As others have mentioned already, using VBA to refresh the linked tables'
Connect property will work for your situation and it should go through and
update all your linked table information very fast.

Good luck with your project.