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:
- 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.
- 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).
- Place all files in the new folder.
- Change the name of the frontend database e.g. by appending "Old" before the ".accdb"
- Start MS Access 2007 and create a new database with the original name in the proper location.
- Open the new database and directly close the default "Table1"
- Click on "All tables" in the NavigationPane and chose "Category navigation / Objecttype"
- Select "External data / Import / Access" and chose the option "Link...."
- Navigate with the button [Browse] to the Backend database and press [Open]
- Press [OK], select only the table(s) with the "Attachment" datatype field(s) and press [OK]
- Chose "External data / Import / Access"
- Navigate with the [Browse] button to the "Old" database and select the "Import..." option.
- Press [OK].
- On "Import Objects" window press "Select All" for every object tab. (There are six!)
- 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)
- Close the "Import Objects" window and go to "Database tools / Database tools / Linkedtable manager"
- Press [Select All] and remove the checkmarks for tables not in the backend database.
- Select "Always ask for new location" (bottom left) and press [OK]
- Select the backend database and press [Open]
- Close the Linkedtable manager.
- The database is now ready for use.
Expand|Select|Wrap|Line Numbers
- 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