473,695 Members | 2,360 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access 2007 Linked Table Manager not working correct...

nico5038
3,080 Recognized Expert Specialist
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment " datatype.

Problem:
When placing a split database in another folder, the Linked table manager should be used to relink the tables. The Linkedtable manager does however refuse to relink tables having an "Attachment " datatype field.

Symptoms:
Open the Linkedtable manager and select a linked table with an "Attachment " datatype field. Now try to relink the table to another folder. Access will throw an error "Invalid path" when the "old" path doesn't exist, or refuse to relink when the path does exist.

Cause:
The systemtable MSysObjects holds a reference to a "MSysComplexTyp e" with the old path. This reference isn't removed when the table with the "Attachment " datatype is deleted.

Resolution:
To get the table relinked there are two options:
  1. Remove the "Attachment " datatype field, relink and add the field again. This is only an option for "empty" or nearly empty tables. An additional problem can be the optionally added libraries in the original database.
  2. The creation of a new database to get an empty MSysObject, the creation of a new link to the table(s) with the "Attachment " datatype field(s) and finally the import of all other database objects (except for the MSys. tables and the already linked table(s).
Steps to move a database holding tables with an "Attachment " datatype field:
  1. Place all files in the new folder.
  2. Change the name of the frontend database e.g. by appending "Old" before the ".accdb"
  3. Start MS Access 2007 and create a new database with the original name in the proper location.
  4. Open the new database and directly close the default "Table1"
  5. Click on "All tables" in the NavigationPane and chose "Category navigation / Objecttype"
  6. Select "External data / Import / Access" and chose the option "Link...."
  7. Navigate with the button [Browse] to the Backend database and press [Open]
  8. Press [OK], select only the table(s) with the "Attachment " datatype field(s) and press [OK]
  9. Chose "External data / Import / Access"
  10. Navigate with the [Browse] button to the "Old" database and select the "Import..." option.
  11. Press [OK].
  12. On "Import Objects" window press "Select All" for every object tab. (There are six!)
  13. Caution: On the "Tables" tab the "MSys..." tables and the table(s) with the "Attachment " datatype field(s) must be deselected. (Click on them to get a white background)
  14. Close the "Import Objects" window and go to "Database tools / Database tools / Linkedtable manager"
  15. Press [Select All] and remove the checkmarks for tables not in the backend database.
  16. Select "Always ask for new location" (bottom left) and press [OK]
  17. Select the backend database and press [Open]
  18. Close the Linkedtable manager.
  19. The database is now ready for use.
When there are references to move, use this function in the "Old" database:

Expand|Select|Wrap|Line Numbers
  1.  Function fncShowReferences()
  2. '-----------------------------------------------------------
  3. ' Input       : None
  4. ' Target      : Generating function to create the references in another database
  5. ' Created By  : Nico Altink 28/oct/2007
  6. ' Last changed: Nico Altink 28/oct/2007
  7. '-----------------------------------------------------------
  8. Dim ref As Reference
  9.  
  10. Debug.Print "Function fncCreateReferences()"
  11. Debug.Print "' Skip errors for available references"
  12. Debug.Print "On Error resume next"
  13.  
  14. For Each ref In Application.References
  15.    ' Skip the builtin references
  16.    If Not ref.BuiltIn Then
  17.       Debug.Print "Application.References.AddFromFile (""" & ref.FullPath & """)"
  18.    End If
  19. Next
  20.  
  21. Debug.Print "End Function"
  22.  
  23. End Function    
  24.  
When this function is executed it will create a function in the Immediate window to place all references. Copy/Paste this into a module of the new frontend database and Run (F5) the function.
Oct 28 '07 #1
2 15620
steal32
2 New Member
Nice article. Here is one more way so that you can get rid from this error Check your DSN settings and make sure you can connect there. Another thing to check is ownership and permissions on the other tables. Access won't let you link to or display tables you don't have permissions on. Use another tool on that
machine (like Query Analyzer) to test to see if it's a connection
string or permissions problem. Or it may be database corruption so use access repair utility

Thanks
May 30 '08 #2
bkernan
8 New Member
Thanks for this post, i have a very similar issue with copying my access dbs and getting stuck on an invalid path which I can only find to be stored in MSysObjects.

I'll try your solution and post if it works for others who have a similar issue.
Apr 18 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
9461
by: Bruce | last post by:
Greetings! After finally figuring out that the Linked Table manager is no longer an add-in in Access 2003 I now discover that it does not display any of my linked tables in the 'select linked tables to be updated' list. Although I have about 30 linked tables in my application, absolutely none of them are showing up in the list. I compacted and repaired, but that didn't have any effect. Has anyone else seen this problem? If so, how is...
2
6247
by: Ian Davies | last post by:
Does anyone know how to open the linked table manager using VBA code behind a comand button in Access2000 Ian
2
4321
by: Wayne | last post by:
I've just performed a reformat and reinstalled Access 2000 and 2003 and as usual the linked table manager in Access 2003 is broken ie. it doesn't show any tables. I've gone through the process of re-registering Accwiz.dll as outlined in the MS Knowledge Base and although this process has worked for me before, it hasn't worked this time. There are still no tables showing. The article in the knowledge base includes this line: "If the...
1
3556
by: Tom | last post by:
Im beginning to dislike Access 2003. I need to update the links on some SQL tables and my linked table manager is empty. Can anyone instruct me on how to populate this??? Thanx Tom
0
761
by: andyk | last post by:
I have a database on a network drive, that needs to be used by various people throughout the state on their laptops when their laptops are not connected to the network. What I have set up so far is a main database where the tables are held. I also have a database where all the queries, macros, and forms are held. This second database contains a link to the tables in the first database. When someone needs to use the database, they will be...
3
1082
by: Parasyke | last post by:
Can anyone guide me through changing the location of my linked tables through the Linked Table Manager? (MS-Access 2000) Here's what I've done: I'm in the front-end application on a split database of course. I open the Linked Table Manager, Select all Tables, and check "Always Prompt for new location. This brings up a window which displayed the possible sources which starts out as My Documents. I chose Network Places and that brought...
1
4092
by: tbeer | last post by:
Hello. I have created a 2007 database to help organize a golf outing event which requires the collection and tracking of a lot of linked data. Everything in my dbase is linked to my Main Business Contacts table which consists of 600 businesses who participate in and sponsor our event (primary key is BusID). From these 600 records I have linked tables consisting of 1) different office locations, 2) individual contacts within those office...
1
7586
by: franc sutherland | last post by:
Hello, I have a client who uses Access 97, but does not have the Linked Table Manager. The system is split into front and back end. Is it possible to install the Linked Table Manager into Access 97? If not, how does one manage the table links? Thanks,
1
3283
prn
by: prn | last post by:
Hi folks, Here's a weird one. We have a fair number of Access applications where the front end sits on a user's desk somewhere, but the data sits on a SQL server. We're in the process of retiring a SQL Server box that contains some of those databases and I've got the responsibility for the Access end of the changes. In most of the cases so far, it's been a matter of trotting over to the remote location and modifying the DSN (Control Panel >...
0
8585
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9004
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8864
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8838
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7682
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5842
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4351
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3024
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1986
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.