Access 2007 Assets copy front end db to new directory Issue

[bkernan]

I am developing an Access 2007 database using the Assets template in a dev directory on our server. It's split and the back end data portion of the database is stored in the same directory as the front end. The front end has customized reports, queries, forms that I need preserved across future versions. This front end and back end will need to be moved to another server when we want to 'go live' with this database so users over a network can use it.

The problem is when I copy the front end and back end to another directory on the server (say sibling level), relink all the tables, and test that everything works fine, I am unable to delete any of the folders in the path to the Original back end database. This is because in the MSysObjects table there is a record which stores the path to the original back end db, although it is not used or referred to anywhere else in my code/tables/controls/etc.. This record is only created when you copy the database file (using .accdb btw). This record also has a field that says something along the lines of '...ComplexType ...' and has '6' in the type field (which i believe means its the type of table).

I receive an error message like 'path [path to original dev backend db] does not exist', and, of course, I am unable to edit the record in the MSysObjects (normally a good thing).

Does anyone know how to:
a. remove this record from MSysObjects
b. edit this record to point to the new/correct location of the back end db
c. copy a database front end without this record getting inserted into MSysObjects
e. other solution

Note: Accidentally posted in VB section - reposting here in correct Access/VBA category

[RuralGuy]

Are you talking about the Database field of that table? That changes when you use the Linked Table manager. AFAIK, Access does not keep track of your original development location. Maybe you are looking at a table that is no longer in the BackEnd and so can not be linked.

[bkernan]

Hello RuralGuy, thanks for your help.

All the links to the tables in my Backend are there and good, and I can update them with the Link Table Manager with no problem. And in the Backend db in my new location, all the tables I need are there.

There is an additional link stored in the MSysObjects of the new db that points to the original backend location. I don't know how it gets there because it is only created when I copy the front end to a new location. In this system table MSysObjects, I also see the links to the tables (which are correct after I relink them to the new be db) stored in MSysObjects.

The system table MSysObjects in the original location does not have this record. I'll post the exact records data here tomorrow to better show what it is that is being automatically inserted.

[RuralGuy]

As I asked, is this in the Database field of the table? Is it pointing to a particular table in the Name field of that table?

[bkernan]

Oh sorry, I'll let you know tomorrow what it says exactly, but the path is the the Database field

[RuralGuy]

I'll keep an eye out.

[bkernan]

Hello,

yeah the Database field is the path to where I first created the database backend before moving it

the Name field of this record is: MSysComplexType VH_1CA2435D69A7 42F498B36C3C73E 48216

This link below explains that this issue is most likely caused by the new Attachments field

http://bytes.com/topic/access/insights/729433-access-2007-linkedtable-manager-not-working-correct

We decided to move our db up as one, then split it on our live server, as we do not get this error when the db is not split.

It would be interesting to learn more about why/how this path can/cannot be changed.

Thanks for your help anyway : )

[RuralGuy]

Hmm...Does the attachment field still work as expected? I'll admit I have not used them yet.

[bkernan]

In this case (when the invalid path alert comes up) I cannot open my Datasheet, so I cannot get to the Attachments.

When I restore the path to the original location, everything works fine. The original database is not even Used (because I relinked my tables).

It is peculiar.

[RuralGuy]

Makes me suspect that the attachment is not really in a "field" in the db but simply linked. If the link were a UNC value it might work.