Dynamic Update of DAO Using Model Database
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
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 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.
Download the VB 6 source code from my site.
More tips link to:
No personal e-mail questions :-)