Solved

Where is the link information stored for a table

Posted on 2009-04-08
14
213 Views
Last Modified: 2012-05-06
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)?
0
Comment
Question by:Andy Brown
  • 5
  • 5
  • 4
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can not change the information in the msysobjects table directly.. and i will not ry doing that
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can the information from the msysobjects table

select [name],[database] from msysobjects where type=6
0
 

Author Comment

by:Andy Brown
Comment Utility
OK - but it just seems really slow when re-linking between other databases (especially if they are in use).
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if the link is using odbc,

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

0
 

Author Comment

by:Andy Brown
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
it won't work..
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
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
 

Author Comment

by:Andy Brown
Comment Utility
That's great advice (as always).

As for the linking to the alternate databases - how would you do it?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
 

Author Comment

by:Andy Brown
Comment Utility
"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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:Andy Brown
Comment Utility
Thanks guys - that was some really good information - and as for the link performance the MX post really helped.

Really appreciated.  
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now