Update an Access Database with a Model's Schema using VB6 and ADOX - June 2003

In September 2000 I published a tip that updates an Access Database with a model's schema using DAO. Click here to see that tip. I have been using ADO for database access in most application I'm now developing so I updated the tip to use ADO/ADOX to provide the same functionality. August 2003 - The tip now supports VB.Net - click here for the VB.Net version.

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 very 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 was using DAO but am now using ADO/ADOX to perform the 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.

Modified December 14, 2003

ADOX returns field names in alphabetic order - the 'design' order is lost. The code I wrote iterates through the Jet table and adds fields in the order presented by ADOX. Thierry Waty modified the code to first retrieve the table schema using an ADO record set. A record set returns data in unsorted order. He modified the code used in this demo to use the 'record set' concept. We provide that modified code. See below to download it. Thierry lives in Belgium and develops VB applications and web sites. Link to: http://www.waty.org/ to view Thierry's family web site.

Updated June 11, 2003

Update Individual Database Elements - I added four methods, UpdateTables, UpdateIndexes, UpdateRelationships, and UpdateStoredProceduresAndViews. Method 'Update' is still supported but now the updates can be separated. I made this enhancement to allow a database to be copied. So the steps are:

VB Code Used to Update A Database

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

Private Function DoUpdate()
  Dim obj As New DBUpdateADO.cUpdateADO 'define the object
  Dim connDB As New ADODB.Connection    'connection to the application database
  Dim connModel As New ADODB.Connection 'connection to the model database

  With obj
    connModel.Open mcsProvider & Trim$(Me.Text1(0).text)
    connDB.Open mcsProvider & Trim$(Me.Text1(1).text)

    If .Update(connDB, connModel) Then 'do the update
      'success - do success stuff
    End If
  End With
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:

Object Browser
Update using ADOX methods and properties.


Results Class

Property or Method Description
Class cUpdateADO  
Methods:  
Update Causes all the tables, fields, relationships and queries to be updated.
     -or- Update the elements separately.
UpdateTables Update all table structure using the model database's table and column schema. Must be done first if elements are updated separately.
UpdateIndexes Update all indexes using the model database's index schema.
UpdateRelationships Update all relationships using those relationships found in the model database.
UpdateStoredProceduresAndViews Update all stored procedures and views.
Properties:  
connDB An open connection to the application database.
connModel An open connection to the model database.
sAppPath Write the log and error log files to this path, unless a fully qualified file name is specified in the next to properties.
sFileNameLog Fully qualified file name for the 'results' log. Default:
App.Path & "\UpdateDatabaseWithModelUsingADOX.log"
sFileNameErrorLog Fully qualified file name for the error log. The log is only created if there are errors. Default:
App.Path & "UpdateDatabaseWithModelUsingADOXErrorLog.log"
bSkipWriteToLog If True do not write a results log. Default: False
cResultsTables
cResultsColumns
cResultsIndexes
cResultsRelationships
cResultsStoredProcedures
cResultsViews
Result counters. See class cResults. Results are reported in these objects.
Class cResults  
iAdded The number of items added.
iReplaced The number of items replaced.
iSkipped The number of items not added or replaced - they were skipped.

Uses ADO/ADOX 2.7

I used ADO/ADOX 2.7 in the DLL and the 'test' program. You can probably use earlier versions. I have not tested it with earlier versions.

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' buttons 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.

Modules Used in the Tip

Module - Click for More Info Description Used in the Demo To
cUpdateADO.cls The class modules which exposes methods used in this tip. Calls public functions in modUpdateDatabaseWithModelUsingADOX.bas. The subject of this tip.
cResults.cls Result counters. The subject of this tip.
modUpdateDatabaseWithModelUsingADOX.bas Update the application database using the model's schema. The subject of this tip.
modCreateGUID.bas Create a GUID. Create a relationship name using function:
CreateGUIDWithPrefix
modCreateRelationshipUsingADOX.bas Create an Access relationship using ADOX. Create relationships.
modShellExecuteUsingFileName.bas Launch a file using it associated application. Launch MSAccess and open the selected database.
cDialog.cls Browse for a file. Used to browse for an application or model database.
cDialog.bas Use with cDialog.cls. Constants used by cDialog.cls.
modConnectToWebSite.bas Connect to a web site. Show this info when Help/Info is selected from the demo.
modInfoAbout.bas Info and about help. Provide help via the web and 'about' info.
modAddBackSlash.bas Check for a right backslash and add if necessary. Add backslash to App.Path.

Download VB Code

Click here to download the VB 6 Source Code [103k] 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.

Click here to download the VB 6 Source Code [38K] as modified by Thierry Waty. See above. It is provided as received.

Usage Note

This code is not currently being used in a production application. It is being used in a 'beta' application. Please report errata to larry@larryrebich.com.

Previous Tip   Tip Index    Next Tip

Home

Updated 2010/12/28