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.
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
.sAppPath = AppPath()
.connModel = connModel
.connDB = connDB
If .Update() Then
sLog = .sFileNameLog
sLogError = .sFileNameErrorLog
bAnyErrors = .bAnyErrors
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:
Update using ADOX methods and properties.
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
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.
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.
This code is not currently being used in a production application. It is being used in a 'beta' application. Please report errata to firstname.lastname@example.org.
Previous Tip Tip Index Next Tip