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 :-)
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL