Author: Garry Robinson
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.
Sample database is suited to all versions of Access. When you download the sample database, you will find out how to
If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.
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.
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
Get Good Help Here
If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia