MS Access 2010 Split Database with ACCDE Files

Question

I am new to Access.  I have several questions about database splitting and ACCDE files.  Your comments as always are greatly appreciated.

Here are the questions:

1. Backup

Please confirm or comment on the following:

1. I assume it is necessary to backup both the back end and front end for a split databases as follows:  
    1. The back end should be backed up regularly (eg., daily) whenever data is being changed by users.
    2. Backup can be accomplished either by an Access backup or a data center backup.
    3. The accdb file, which I am assuming still holds (or accesses) the data as well as the objects (tables, forms, queries, reports, etc), should be backed up whenever objects are changed.

2. ACCDE
   Please confirm or comment on the following:
   1. These don't need a backup.
   2. They should be re-created every time there is a change to the front end (objects) of the database.
3. Changes
   1. How and what are best practices for making changes in development with out affecting production front or back ends?
   2. How and what are best practices for putting changes into production?
   3. How do you know/deep track of which accdb version is associated with which back end version and which ACCDE?

Answer 1

Robert, 

Let me explain my setup and it may help you.

I have a development folder on my local drive. This folder is synced (using SyncToy a MS freebie), with a development folder on our server. I do all my testing and development on my local drive. When I am satisfied that all changes work, I then place a copy of the front end in my front ends folder on the server. We then use AutoFEUpdater (http:\autofeupdater.com) to manage deploying the changes. The user clicks on a desktop shortcut, the server is checked to make sure the user has the latest version. If not the new version is downloaded. 

There are other, similar updaters if you want. 

As for table changes, these are made during off hours to the production back end after testing. 

On point on your question under 2. You are always working in an ACCDB version. The ACCDE is created only when ready for deployment. So as long as you have a backup of the ACCDB you don't need one for the ACCDE.

Answer 2

Thanks again Tom.

Answer 3

Thanks so much for this guidance.  I have been doing homework, reading, but haven't gotten to VBA etc. yet.  I am in a small non-profit operation, where the production environment for applications is simply a different network drive letter.  I will be able to submit updated versions of the database on request.

Answer 4

You have been doing your homework.  Good job.

To expand on your third point, Changes:

You need to keep your own private copy of the front end where you can make and test changes.  Depending on how extensive the changes are, it may take months before a large set of coordinated changes can be put into the production environment.  You will want to make frequent local backups while you are developing and debugging changes so don't wait for the daily IT backup.

You will also need a private copy of the back end for testing your changes to the next version of the front end.  Personally, I usually keep the same test back end with phony data that stresses the boundary cases.  I also periodically copy the production back end to my local disk to test against real data.  You can relink the front end to whichever test back end you want to use while debugging and evaluating your changes.  There are several VBA procedures on the web that can be used to relink a front end to any designated back end or you can just use the Link Table Manager built into Access if you don't relink very often.

Making design changes to the back end should be rare, but are also inevitable.  I do not trust myself to remember every step, even if I write them down, of manual changes to a test back end so they can repeated without error on the production back end.  So, I create a VBA procedure in the front end that makes all the changes to the back end using VBA/DAO code and/or SQL DDL statements to add/alter tables, relationships and fields.  This way I can test that the changes will work when it comes time to really update the production back end and have high confidence that running that procedure will apply the changes correctly.  Of course, all users must be out of the database when the backend is being modified.  But, barring a catastrophic failure, that should be a pretty short period of time while the update procedure runs.

Answer 5

Good work. You got everything right.

Back-end backups should ideally be taken when there are no users in the database. This is of course hard to do in a 24/7 business, but in all other cases users should exit the app when they go home.

Front-ends can be deployed by an updater program. If you search the web for "Access front end updater" you'll find several. Since users launch your app through this updater they are automatically on the latest version and no further BE version tracking is needed. If you feel you need something more robust, nothing stops you from having a table in the BE that essentially says "I require FE version X or greater" and some VBA in your startup code that compares X with the current FE version.