How to Setup an Access Version Number System

Author: Garry Robinson

Overview - This page will show you how to add a version number to your database so that you can ask your users what version of your database they are actually using.

One of the most basic of tasks for a person developing an Access software solution is to ensure that the development of the software doesn't interfere with the live version of the database. There are many reasons for doing splitting development databases from live database because in the end you will find that your software changes will cause havoc to the users of the database. Trust me, software changes are not always a good thing.

One of the issues that occurs as a consequence of developing database software somewhere else is that you can never can be certain that a user is actually using the database that they are supposed to. Reasons for this include the current live version may not be installed as you thought or the person is running an old copy from a local drive and you they forget to tell. Anyway the best solution to this problem is to add a unique version number to the database in a prominent place so that the user can tell you what version they are running when you try to diagnose a problem.

The Demonstration Database

When you download the version database, you will find out how to

1) setup a simple table to store the current version number/details for your database. 

2) display those details on a prominent form in your database

  Click here for the download file for both Access 2000 and above and Access 97.

Figure 1 - The Access software version demonstration form

NOTE: This Version number system is made even easier when you use the Access Workbench because you do not have to open the database to find out what the version number is. Find out about the Access Workbench here. Trust me, when you are working on more than 10 databases, a standard approach like this can save you a lot of time "Garry Robinson"

NOTE 2: The table uSysDBVersion has been hidden (by design) as system table. It is hidden as a system table, a trick that I mention in my book (chapter 3).  You actually need to use a query if you want to modify the data manually. 

Discussion's On Linking From Chapter 4 of Garry's Book On Access Security

Delivering a New Front-End Database with Linked Tables

Now that you’ve split your database, the next issue you will encounter is that the physical location of your development back-end database won’t be the same as that of the live back-end database. The exception to this issue occurs in the early phase of development, when the person(s) who are testing your database are using local drives such as C:\ or D:\. If that is the case, make the location of your development back-end database match the location of your live back-end database, and save yourself some of the steps here. Of course, if you are using local drives for development and even to store important data, make sure that you have a backup system in place and, even more importantly, make sure that you can retrieve it.
Now let’s see what you have to do to install a new front-end database as the live database.

1. Before shipping a database to the DBA, always put a version number on the startup form in the database. This way, you can actually verify whether the version someone is testing is the latest update.

2. Before shipping a database, it is always a good idea to make a backup copy of the database. I make a compressed .ZIP file and give the .ZIP file a name that includes the version number. Then I ship the compressed .ZIP file to the DBA.

3. Once the DBA has the database, he or she should replace the existing front-end database with the latest version. Generally, the DBA should have a recent backup of the current live front-end database, but if he or she doesn’t, he or she should make one. Having a recent backup is necessary in case someone has changed something in the database and the DBA needs to recover those changes.

4. The DBA should now open the front-end database that contains links to the tables. In Access 97, choose Tools ~TRA Add-ins ~TRA Linked Table Manager. In Access 2000 and later, choose Tools ~TRA Database Utilities ~TRA Linked Table Manager.

5. In the Linked Table Manager dialog box, click Select All (shown in Figure 4-5). This action selects all the linked tables in the database. Now click OK.


Figure 2. Update the links by clicking the Select All button.

6. Choose the location of the back-end database by using the Find File dialog box and click OK. This action refreshes the links to the tables in your back-end database.

7. Check that a couple of the tables are working correctly before releasing the database to your users.
 

Click here to read about Garry's book on Access protection and security

Other Pages at VB123.com That You May Want To Vist
Remote Queries In Microsoft Access
Processing E-Mail Orders using Outlook and Access
Building Your Own Wizard User Interface
Replace Your File API’s With The FileDialog Object

Click on the button for the next help page in this Access Loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals