Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Where is the link information stored for a table

Posted on 2009-04-08
14
Medium Priority
?
236 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24099427
you can not change the information in the msysobjects table directly.. and i will not ry doing that
0
 
LVL 75
ID: 24099449
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24099455
you can the information from the msysobjects table

select [name],[database] from msysobjects where type=6
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24099486
if the link is using odbc,

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

0
 

Author Comment

by:Andy Brown
ID: 24099497
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24099511
it won't work..
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 24099533
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
ID: 24099634
That's great advice (as always).

As for the linking to the alternate databases - how would you do it?
0
 
LVL 75
ID: 24099708
"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
ID: 24100620
"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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 24100866
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
ID: 31568134
Thanks guys - that was some really good information - and as for the link performance the MX post really helped.

Really appreciated.  
0
 
LVL 75
ID: 24109412
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

782 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