Update an Access Database with a Model's Schema using VB.Net and ADOX - August 2003

In June 2003 I published a tip that updates an Access Database with a model's schema using VB6 and ADOX. Click here to view that tip. And 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. This month I've converted the code to use VB.Net and ADOX.

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.

VB.Net Code Used to Update A Database

Add project DBUpdateNetDLL or compile and register the DLL. Then use code similar to:

Public Function DoCreate(ByVal connModel As ADODB.Connection, _
   ByVal
connDB As ADODB.Connection, _
   ByRef
sLog As String, ByRef sLogError As String, _
   ByVal
bAnyErrors As Boolean) As Boolean
   Dim
obj As New DBUpdateNetDLL.cUpdateNet

   With
obj
      .sAppPath = AppPath()
      .connModel = connModel
      .connDB = connDB
      If
.Update() Then
         sLog = .sFileNameLog
         sLogError = .sFileNameErrorLog
         bAnyErrors = .bAnyErrors
         Return
True
      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:

cUpdateNet Properties and Methods
Update using ADOX methods and properties.

cResults 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 two 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"
bAnyErrors Where they any errors?
bSkipUpdateRelationships Don't update relationships.
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.

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

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.

Download VB Code

Click here to download the VB.Net Source Code [186k]. You will need to reference the Class Library if you plan to use it in your application. Click here to review the process used to add a reference to a Class Library.

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 2009/12/01