Where is the link information stored for a table

I have a central database that monitors several other databases - by linking to the tables in those databases.  

Instead of re-linking to each database, which can be slow if others are using it, can I simply change the link source somehow (perhaps MSysObjects or something like that)?
Andy BrownDeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Andrew ... see this:

http://allenbrowne.com/bug-09.html

Scroll down to 'Tables: SubdatasheetName'

Be SURE all tables in the BE are set to None.  Also, in the FE - set that property for the linked tables too.  This simple fix can make the difference between 5 minutes and 30 seconds to link say 30 tables.

mx

0
 
Rey Obrero (Capricorn1)Commented:
you can not change the information in the msysobjects table directly.. and i will not ry doing that
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In the MSysObjects table:

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

You can only change the link via Code, or manually relink.  You cannot directly modify the MSysObjects table.

mx
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
you can the information from the msysobjects table

select [name],[database] from msysobjects where type=6
0
 
Andy BrownDeveloperAuthor Commented:
OK - but it just seems really slow when re-linking between other databases (especially if they are in use).
0
 
Rey Obrero (Capricorn1)Commented:
if the link is using odbc,

select select [name],[connect] from msysobjects where type=4

0
 
Andy BrownDeveloperAuthor Commented:
No it's not using ODBC.

So what would happen if I ran an update query based on MX's suggestion to replace the value in the [database] field, with that of the required database?
0
 
Rey Obrero (Capricorn1)Commented:
it won't work..
0
 
Andy BrownDeveloperAuthor Commented:
That's great advice (as always).

As for the linking to the alternate databases - how would you do it?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"based on MX's suggestion "
That wasn't really a suggestion - just a way to list the connections strings.

"can I simply change the link source somehow"
But that is basically 're-linking' ...

As far as the 'slow' issue ... I would really get that property set to None.  I can't begin to tell you how much that can affect db operation, especially linking. Before I discovered that several years ago ... it use to take 5 minutes at one client to relink (with a new version of the app) 5 minutes.  After I made that change ... 25 seconds.  Cut and dry.

mx
0
 
Andy BrownDeveloperAuthor Commented:
"based on MX's suggestion " - I know

"But that is basically 're-linking" - How would you re-link

"As far as the 'slow' issue ..." -  Genius!!!!
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
see this links

Relink tables from different datasources
http://www.mvps.org/access/tables/tbl0012.htm

Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm
0
 
Andy BrownDeveloperAuthor Commented:
Thanks guys - that was some really good information - and as for the link performance the MX post really helped.

Really appreciated.  
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Grade:  A - "Thanks guys - that was some really good information - and as for the link performance the MX post really helped.

Really appreciated.  "  

You are welcome ...

mx
0
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.