Introduction

I recently found myself as a developer / adopted-owner of several database applications designed in Microsoft Access. I’ve touched on a few of the million difficulties with the program before, but one of the most difficult tasks I have as a developer is deploying updates to these applications. Databases used by five or fewer users are not an issue, as it’s easy to tell them when I’ll update their copy and when they can begin using it again. Deploying updates really becomes a problem when you have thirty to fifty users in the same database from early in the morning to late at night.

When I started out, my only choice was to come in during the weekend and complete the updates, but that does not work out well for me. In order to simplify my work I decided to figure out what I can do to ease my pain. Before I did any research, I had to list my limitations:

1. Users must be completely out of the database to avoid data loss and complications.
2. I need to prevent the database from being accessed during the maintenance downtime.
3. I need to make sure I transfer all old data to the new database, as data can not be lost.

First Step: Log All Users Out!

The first step is to make sure that all of the database users have exited before any work can begin. Currently I simply schedule a time with the department manager and they let everyone know when to logout. The problem is that if a single person forgets to quit the application, I have to track them down before I can begin. This process eats up the scheduled block of maintenance time like nothing else, and by the end I may not have enough left - even though I accounted for some delay to begin with.

I asked the question can I kick all remaining users out of an application remotely? The answer is yes, but I can’t just kick everyone out without some kind of warning. After a lot of searching I found Auto Logout Users for DB Maintenance. This code scans a checkbox on a form and displays a timer and warning to all users, and then closes the database once the timer reaches zero.

You may download the auto-logout sample directly.

Obviously the sample that the article above provides is not perfect. The warning form blocks users from accessing any form until they click OK, but then the same warning pops up ten seconds later. This would really be annoying to an end user trying to finish their work. I adjusted the warning to popup every minute and I extended the countdown to five minutes instead of three.

It’s a bit ugly and insecure how the sample scans for the request for shutdown. I have no guaruntee that someone else exploring the database won’t find the hidden field and use it incorrectly, but I guess I can look into ways to secure that as well. More on that later.

Afterwards, I pretty much have a working way to log all users out of the database.

Second Step: Keep Them Out!

The next issue is preventing users from simply logging back in. Maybe they forgot or didn’t know about the maintenance downtime, I need to automatically prevent them from distrubing the database during the update.

My simple workaround for this is to quickly move the production database to a temporary (and private) folder for the maintenance. I replace it with a simple database that pops up a form letting them know what’s going on - why their database is not there and when it will return. This way they know when to return and are not confused by shortcuts that don’t work.

Third Step: Time to Start!

Now, I have the database to myself and it’s time to begin the update (after making a backup of the original just in case). This next step can change things a bit because it’s time to make a decision. Depending on the specific situation, any number of these may be the right option for your updates. Your choices for updating the system are:

1. Copy all of the tables from the old database into the updated database.
2. Copy the data from each table to the updated database.
3. Split the database using the Database Splitter tool so that data exists seperately from the forms, reports, queries, etc.
4. Replicate the database, which allows syncronization of the data (but disables queries that affect table structure in the child version).

The first option of simply copying all tables can be problematic if changes to the table structure or relationships were modified. If the table structure and all relationships exist in the updated database exactly the same as they do in the old copy, then simply replacing the old ones will work fine (a few minor changes can always be made manually if needed, so very minor changes would still allow for this type of update as long as you remember them).

Copying the data in the tables to the updated tables can cause problems. You may not copy all of the data, you may not delete all of the test data, etc. If the table structure has changed, you can’t just paste the old data in without reformatting the data properly. Plus, you may miss tables and have half-updated data - no one wants that!

The third option is splitting the database, which sounds like a great idea. It’s the closest idea to the seperation of design and content available in an Access application. One database holds your data and only your data, while a second database holds the forms, reports, and queries. This way, you can simply place a copy of the updated database in with the live-data tables, and be on your way. This sounds like the best solution as it saves time.

One feature of splitting a database is that each user could have their own version of the interface database. That sounds like way too much inconsistency, which is never a good thing.

But having so many linked tables will be a performance hit, and would be even worse for each table and user you add. For my target user group though, this data seperation is probably the right scenario for this option.

Ignored Options

I had also looked into a scheduled update system, that would synchronize the Access data and then activate a batch file that copies the update database and replaces the old one. The main problem with that is that if you have subtle changes in the table structure you would not be able to properly copy your data. However, I am willing to look into this a bit further.

Using software for data transfer between the client and developer database is certainly an option, but you’re going to need to supervise it anyway when there are changes in the table structure that prevent a simple copy and paste of data. For this type of data transfer, I don’t think that spending so much money on additional software is the answer.

If you have access to a programming language environment you can create a custom script to interact with the MDB file and make the appropriate table changes and data transfer at a scheduled time. Of course, I assume that if you have access to such an environment, you have better tools available than Access to begin with.

Conclusion

Access is a bulky application that is becoming outdated with each passing day. Access 97 is still relied upon heavily, and Access 2003 is still considered modern. Groups that I work with have all admitted that our most recent internally-developed systems are based on technology that’s almost three years old. Access has no real method of deployment, it’s missing basic support for revision control, lacks useful features found in web-based tools like AJAX, RSS, and a wide array of other issues. I only use Access because I must and when I do, I try my best to keep everything as clean as I can.

The auto-logout feature above is something that I will play with some more, and maybe use one day. Until then, I will simply continue to schedule a time with the users, replace the production copy with a stand-in, and manually update the data. If I find myself in a situation where splitting the database into a set data and the collection of forms and reports, I will. However, I have not had that perfect opportunity yet.