Update an Access Database with a Model's Schema using VB6 and DAO - September 2000

I recently updated this tip to use ADO/ADOX to perform the functionality. Click here to view the ADO/ADOX version of the tip. Click here to view the VB.Net/ADOX version of the tip.

Introduction

The Problem - You completed your application. You made a number of sales. Your customers are entering data into a database you supplied. Now you are ready to ship a new version. You have announced it to your customers and they want it. You added some tables, a field or two, defined additional relationships and added a couple queries to the database. Now you have a problem. You don't want to ship the new database and force your customers to 're-enter' all their data. What's the solution? You need an automated way to update their database, adding the new tables, fields, relationships and queries.

The Solution - I have been using this technique for some time. I dynamically create a 'model' database with each version of an application. The model contains a 'version date' field in a control table. I ship the model with the software. I do not ship the 'live' database. When the user executes the application for the first time I copy the 'model' and make it the 'live' database. This is not done if the 'live' database already exists. Then when the user opens the 'live' database I compare the 'version date' field between the model and the 'live' database. If the dates are different then I use the model's database schema, including tables, fields, relationships and queries to update the 'live' database. I use VB's DAO facilities to perform this task. Lastly I update the 'version date' in the 'live' database with the date found in the model. That prevents further updates to the 'live' database - until I ship a newer version of the application with a newer model database.

Encapsulated - I recently created a new application and thought it was time to encapsulate the process in an ActiveX DLL. So I did and created a DLL I call 'DatabaseUpdateUsingDLL.dll'. It contains one class I call 'cUpdateDAO'. I then created a 'test' program to exercise the DLL. After some clean-up I decided to make it the tip-of-the-month.

VB Code Used to Update A Database

First you need to register the DLL then make a reference to it [TB Database Update Using DLL] in your application. Typical VB calling code:

Private Function DoUpdate()
    Dim obj As New cUpdateDAO 'define the object
    With obj                  'add some properties
        .sDBModel = Trim$(Me.Text1(0).text) 'the copy from db
        .sDBApplication = Trim$(Me.Text1(1).text) 'the update to db
       
.bSkipSettingAllowZeroLengthField = False 'default
        If .Update() Then 
            'success - do success stuff
        End If
    End With
    Set obj = Nothing
End Function

It is your responsibility to determine if the updated is needed. As mentioned above, I open both the model and live databases and compare a date field in a 'control' table. If the model date is later than the date in the live database then I perform the update. After the update is completed I update the live database's control table's date field with the date found in the model. Testing for a needed updated is usually performed in my 'OpenDB' routine.

Properties and Methods

Properties and methods exposed in the dll are shown in this 'object browser' window:

Property or Method Description
sDBModel  The fully qualified file name of the 'model' database. Can be supplied when calling an update method.
sDBApplication  The fully qualified file name of the 'live' database. Can be supplied when calling an update method.
bSkipSettingAllowZeroLengthField Set this property true to skip setting all text and memo fields to allow zero length fields.
Update  Causes the tables, fields, relationships and queries to be updated. I superset of the other 'Update' methods.
UpdateTables  Causes just the tables and fields to be updated. Note that if you have just added a field to a table only the field will be added to the existing 'live' table.
UpdateRelations  Causes just the inter table relationships to be updated. All relationships are 'reestablished'.
UpdateQueries  Causes just the queries to be updated. The date last modified is used to determine if the query needs to be updated.
Counters  The number of actions taken is reported in the various counters, for example: lTablesAdded. 

Because some of the model's relationships may depend upon new fields and tables you should probably always update tables before updating relationships. Queries are not checked until they are executed.

Stable Code

The significant 'code' contained in the DLL has been used in production systems for more than 5 years. I first wrote the code for an application I developed while consulting for a major computer manufacturer. The code is also contained in another major consumer application. The VB source code is provided - see below.

Uses DAO 3.6 - Jet 4

I used DAO 3.6 in the DLL and the 'test' program. You can use DAO 3.5 or 3.51 if you want to. Just open the VB projects and change the reference. To download DAO 3.6 components or learn more about it click here

Test Program

I created a test program that shows how to use the DLL. The test program's form:

Model Database [from] - Enter the 'model' database's fully qualified name in this text box. Default is 'DBModel.mdb'.

Application Database [to] - Enter the 'live' database's fully qualified name in this text box. Default is 'DBApplication.mdb'.

Find - Use the 'find' button to browse for a database. 

Open - Use the 'open' button to open the database using its associated application. The 'open' routines use 'ShellEx' to open the file with it's associated application. ShellEx is the topic of the February 2000 tip-of-the-month. For more information on ShellEx click here.

Reset - Use this button to create a 'live' empty database. Actually database 'Copy of DBApplication.mdb' replaces 'DBApplication.mdb'. 'Copy of DBApplication.mdb' is distributed with the downloadable VB source code - 'DBApplication.mdb' is not.

Update the Application... - Click this button to apply the updates. Results are shown in the 'results' text box. The database does not need to be opened for exclusive use during the update but you will probably want to make sure that it is not being used in another application. A warning message similar to the following is issued.

Allow Zero Length Fields

By default the table update routines automatically set all text and memo fields to 'allow zero length fields'. After the tables are updated, each text or memo field is examined and the 'allow zero length fields' property is set to true if it was false. Set property 'bSkipSettingAllowZeroLengthField' to true to skip the update. Click here for more info on this topic.

The Test Program Uses modConnectToWebSite

The demo program uses modConnectToWebSite.bas to display this page. For details on the use of that module click here.

Download VB Code

Click here to download the VB 6 Source Code [84k] for the dll and the test program. Both the dll and test program have been compiled using VB6. VB 6 source code is provided for both. Be sure to register the dll before executing the test program.

Previous Tip   Tip Index    Next Tip 

Home

Updated 2009/12/01