Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010

    Unanswered: Access 2007 Split db, issue moving .accdb to new Directory

    Hello and thanks for your time,

    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

    Thank you for your help!

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Congrats on using the split frontend/backend method which I strongly encourage for any project.

    It seems weird that you can't delete the folder where the original db exists since there shouldn't be 'any' ties to that db once you've moved it and re-linked the tables (although 2007 might do some new kind of weird stuff that previous versions don't. But I'm guessing that there's 'something' still tied to the backend old location.

    Here's what I might recommend (which should only take a few minutes to do):

    1. Create a new blank accdb (which will be the new backend) in the live folder.
    2. Import all the tables from the old backend accdb into the new backend accdb.
    3. I'd do the same with the frontend in the live folder. Create a new accdb for the frontend and first link in your tables from your new live backend. Then import all the other objects from the old frontend.

    Make sure to debug/compile the code and compact/repair the accdb in your frontend when done!!.

    Regarding your ideas:
    a. remove this record from MSysObjects - not recommended. Easy to corrupt an accdb doing this.
    b. edit this record to point to the new/correct location of the back end db - again, not recommended.
    c. copy a database front end without this record getting inserted into MSysObjects - not sure I understand.
    e. other solution - the method above will give you the cleanest solution.
    Last edited by pkstormy; 04-18-10 at 15:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 15
    The method provided by pkstormy is better but if you're in a hurry you can try to remove any linked tables from the front-end (working from the original location), move both files (FE and BE) where you want them to be, then reattach the BE tables to the front-end.
    Have a nice day!

  4. #4
    Join Date
    Apr 2010
    Thank you for your quick response!

    The solution you suggested sounds very good! Much better than editing the system tables. We prefer to simply Move a copy of our Access dbs up to our production server instead of creating new files one it. Yet if importing the data and design works just like the original, than this solution should be fine for us.

    My colleagues created a similar db as me with previous versions of Access and never ran into an issue like this, so I do believe it is a newer things.

    Thanks again.

  5. #5
    Join Date
    Apr 2010
    Sinndho, I did try this too, and still had the same problem. Thank you though.

    I deleted all the links to the tables that stored my data, moved the fe and be to the new dir, then relinked. The links to these tables were fine, data was stored in the new be db in its new location.

    What is extra strange is that I can actually delete the original be file, and the new db's will work great. It's when I try to delete/rename any of the folders in the path too it that I receive that invalid path error message.

    original location: devserver\myfolder\myproject\project-backend.accdb
    new location: devserver\projectfolder\projectname\project-backend.accdb

    original location's file can be deleted, but myfolder and myproject folders cannot be deleted/renamed

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts