Access 2007 Linked Table Manager not working correct...

[nico5038]

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 "MSysComplexType" 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:

 Function fncShowReferences()
'-----------------------------------------------------------
' Input       : None
' Target      : Generating function to create the references in another database
' Created By  : Nico Altink 28/oct/2007
' Last changed: Nico Altink 28/oct/2007
'-----------------------------------------------------------
Dim ref As Reference
 
Debug.Print "Function fncCreateReferences()"
Debug.Print "' Skip errors for available references"
Debug.Print "On Error resume next"
 
For Each ref In Application.References
   ' Skip the builtin references
   If Not ref.BuiltIn Then
      Debug.Print "Application.References.AddFromFile (""" & ref.FullPath & """)"
   End If
Next
 
Debug.Print "End Function"
 
End Function    
 

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.

[steal32]

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

[bkernan]

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.