Access Back-End Updater

Microsoft Access has a feature called 'Database Splitter' that moves tables from your front-end database to a new back-end database that can be shared across a network. Multiple people can then access this data simultaneously.

As soon as these people begin creating and modifying the data in that back-end database, the back-end database becomes 'the production data'. The inevitable will happen: if you continue to develop a front-end database, you will eventually need to make adjustments to your back-end database structure.

But here is the problem: there seems to be no way to change the table structure (such as adding a table, renaming a field, etc…) without taking the database out of production. First off, you must:

• agree on when to take it out of production
• grab a copy of the production database
• change the structure of that database
• do a “smoke-test” to make sure you haven't blown anything up
• put the changed version back into the right place
• distribute a copy of the new front-end
• Let people use it, and hold on tight!

As developers, we wish to make this process take as little time as possible.

What this utility does is provide an easy way for you, the developer, to make a quick scripted upgrade of your back-end data structure happen automatically as you release new versions of the front-end.

This changes the scenario above to the following:

• agree on when to take it out of production
• distribute a copy of the new front-end
• run the new front-end for the first time (thus triggering the upgrade)
• permit the remaining users to start up their new front-end.

Older versions of the front-end will no longer be allowed to connect to the back-end (ensuring data integrity) and display an error dialog that they need to be update to a newer version.

It is best to start by importing all of the objects in the Access Back-End Updater database file into your front-end database. Many of these forms will assist you with setting up your update scripts. When you are ready to release your application, you can delete all of the ABEU objects except for those listed below, as these are required by the updater to work.

• Tables:
  • Updater_Databases
  • Updater_Fields
  • Updater_Master
  • Updater_Properties
• Modules:
  • basBEUpdater
  • basFileDialog
  • basINIFunctions
  • basStatusMeter
  • clsBEUpdater
• Forms:
  • Updater_Welcome-ConfigSingleDB
  • Updater_Welcome-ConfigAllDBs
  • Updater_ProgressMeter

Next, create a call to RunAccessBEUpdater() as the first line in your application. You can do this by creating a Sub Routine called Main() and putting Call RunAccessBEUpdater() as the first line. This will ensure that each time your application is launched, Access BE Updater runs to check if it is using the proper data file.

For example, create a module in your front-end database, then create a sub-routine called Main() and add this code to it:

Public Sub Main()
    With New clsBEUpdater
        .RunAccessBEUpdater
    End With
End Sub

If you are confused, take a look at the demo.

• Access BE Updater Home Page
• Bugs & Feature Requests