Update front end with new back end file location

[brharrii]

Access 2010

Objective?: I'm attempting to change the file location of the back end portion of my split database.

What I've done so far: I've used the linked table manager to update the back end location of all tables in the front end portion of the database.

Symptoms:
When making a change to the OLD back end: If I change the name or move the old back end and then try to re-open the front end, Access opens as if I'm opening the application itself without any database associated with it until I restore the OLD back end to its appropriate state.

When making a change to the NEW back end: If I change the name or move the new Back end and then try to re-open the front end, I get a: "Could not find file" error message until I restore the new back end to it's appropriate state.

Does anyone have an idea what might be causing this?

Thank you,

Bruce


------------------------------

Update: Thanks to an old thread that BobLarson had responded to, I've just discovered a query that I think put me one step closer to resolving this issue.

http://www.access-programmers.co.uk/forums/showthread.php?t=231817

When I run this query:

SELECT MSysObjects.Database
FROM MSysObjects
GROUP BY MSysObjects.Database, MSysObjects.Type
HAVING (((MSysObjects.Type) In (4,6)));

It returns 2 results:
1. The path of my old back end
2. The path of my new back end.

Unfortunately I'm not able to update the values from the Datasheet, but this does confirm to me that the old back end is still some how being used by the front end even after my tables have all been updated. I'm not sure how to resolve this currently.... I'll check back in if I figure something else out.

Thanks

 

[CJ_London]

if you have moved the backend to its new location, you need to disable the autoexec/splash form (eg by holding down the shift key when you open the db).

Once you are in the access window you can then use the linktable manager to relink to the new location.

If you just change the name of the backend, access will not be able to find it and generates an error - so again you need to relink as above.

alternative is to copy BE to new location - and open the FE - you will still need to close all forms before using linked table manager

 

[brharrii]

YES! that worked. I opened the database while holding shift and then opened the linked table manager. After linking all of my tables again my database now appears to be completely severed from the old back end. Thank you

Do you know if it's possible to do this with an update query for future reference?

 

[namliam]

Something Bob didnt mention is that the use of sysobjects is NOT supported... Query/use/abuse them at your own risk only!

Assuming ALL your tables are linked tables and linked to the one DB, you can easily find its location using
Currentdb.TableDefs("YourTable").Connect

This contains the full path of the backend DB....

If the database cannot be found, error message and exit... simply on a startup form or macro or something.

 

[CJ_London]

Not sure what you mean by 'this' - how often are you going to be moving your backend?

But to answer your question, you can't do it with a query but you can with vba - search on line for 'access vba relinking tables'.

Alternatively, if you want to access a table in another database (which is not linked) as part of your query you can do this by referencing this table. See this recent link for the general structure

http://www.access-programmers.co.uk/forums/showthread.php?p=1315286&posted=1#post1315286

 

[brharrii]

Thanks for the help folks!