The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Application upgrade - data schema update

Ok, here's the situation:
I have a small consumer oriented desktop application which stores data in a file database (MS Access).  I've added some functionality to the application and modified the schema.

Now I would like existing users to be able to upgrade the application and convert their database to the new schema.

I've considered a couple options:
1) Have the installer copy the new schema, then have the application copy the existing data to the new schema.

2) Have the application update the existing schema using ADOX.

Any suggestions on which route would be the most maintainable for the future?  This seems like a common issue, but I haven't come across many articles addressing it.
MicroISV Guy Send private email
Tuesday, April 12, 2005
 
 
Make dead certain your app won't screw up their data.  Include a way to back up their data prior to updating the schema, and a way to restore it to the old version as a just in case.

It's kind of obvious, but some people just don't think of this stuff somehow.
Aaron F Stanton Send private email
Tuesday, April 12, 2005
 
 
Backup their database first.  Then add/modify the required fields.  Then if things don't work, allow them to restore the old version.
KC Send private email
Tuesday, April 12, 2005
 
 
Hi MicroISV Guy,

You have more options that those you stated. Some of these include:

 - DAO -
Call me old fashioned but I still use DAO when developing MS Access based solution (even when the interface is not Access). DAO was designed specifically for Jet, Access' database engine so it is more tightly integrated to Access than ADO(X) which is more generic. DAO still contains some functionality not available in ADO(X).

 - SQL -
Use can use the DDL components of SQL to make schema changes to your database. These SQL scripts are easy to test and fast to execute.

And without wanting to make too much of a point about it, it's fair to reiterate the first two respondents: always demonstrate a healthy degree of paranoia and backup the entire mdb before making any changes to it.

Good luck

Marcus in Melbourne
Marcus from Melbourne
Tuesday, April 12, 2005
 
 
Thanks for the comments.  Yes, I'm making sure the user won't lose their data.  Rather than modify the existing database, I'm copying the existing file to a new file, and then modifying the new file.  The old one will still be available if needed by the previous version of the application.

I've used DAO instead of ADO for accessing Jet databases in the past (to minimize dependencies), but I didn't know DAO could manipulate the database schema (I thought ADOX was required for this).  Ditto for DDL scripts.  How are they executed against a Jet database?

I've seen some other small file based databases out there that work with .NET (eg. Firebird - http://www.dotnetfirebird.org/).  I wonder whether these are better suited for this type of application, or if it's just more of the same.
Robert Smith Send private email
Tuesday, April 12, 2005
 
 
This might also be relevent to your situation. We include a database version number in the database itself so that we can detect and upgrade the schema across more than one version of the application.

This means that the latest version of our application can be used to upgrade the database from any previous version. This approach means that we don't insist that the customer installs every application upgrade but there is always support from the version they are currently on up to the latest version.

For example, if we are applying application V2 to a V1 database then it runs the V1->V2 schema upgrade. If we are applying application V3 to a V2 database then it runs the V2->V3 schema upgrade. If we are applying application V3 to a V1 database then it runs the V1->V2 and V2->V3 schema upgrades.
Mike Green Send private email
Wednesday, April 13, 2005
 
 
Hi Robert and MicroISV Guy
(unless of course you're the same person)

Here's a couple of sample functions to demonstrate manipulating the schema using DAO. The first creates a new query while the second adds a new field to an existing table.

Both of these examples use Northwind as their target database (so you can test them yourself) and I ran them from Excel to test them. Obviously you'll need to change the path of where Northwind.mdb is on your PC.

If you're after reference material I can certainly recommend "DAO Object Model" by Helen Feddema from O'Reilly. My copy's five years old so I don't know if it's still in print (check Amazon). Otherwise get your hands on the Access Developers Handbook for Access 97 (yes I still have my copy) by Ken Getz et al.

Using DAO you can create the entire Access database from scratch via code if necessary. You can create or edit tables, relationships, queries - the whole sheebang. The query sample I've included doesn't have any parameters by it can certainly be done.

I've had circumstances where I've had to re-write ADO(X) code for an Access based solutions for performance reasons. As an example; DAO is much faster at referencing a QueryDef's parameters collections than what ADO(X) is. When users queried the database over a LAN or WAN the ADO code would stop for up to 20 seconds when a reference was set to the query's parameter collection. The equivilent DAO code was almost instantaneous.

(Note that while I'm a diehard DAO fan I recognise that it still has its place as does ADO - ever stried to query SQL Server Analysis Services with DAO?)

As for DDL I don't have any examples as I've rarely used it other than to say it can be done. Understandably most development work typically only utilises the DML side of SQL leaving the DDL and DCL for DBA's.


Function CreateQuery()
   
    Dim wrk As DAO.Workspace
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
   
'  Create DAO workspace and open database within that workspace
    Set wrk = CreateWorkspace("", "Admin", "", dbUseJet)
    Set dbs = wrk.OpenDatabase("C:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb")
   
'  Create a new Query Definition object
    Set qdf = New DAO.QueryDef

'  Set the query properties such as its SQL and ...
    qdf.Sql = "SELECT CustomerID, ContactName " & _
              "FROM Customers " & _
              "WHERE City = 'London' " & _
              "ORDER BY ContactName;"

'  give it a valid name
    qdf.Name = "qryCustomerByCity"

'  Append the query to the database's query definitions
'  collection and refresh the collection
    With dbs.QueryDefs
        .Append qdf
        .Refresh
    End With
   
    dbs.Close
    wrk.Close
   
    Set qdf = Nothing
    Set dbs = Nothing
    Set wrk = Nothing

End Function



Function EditTable()
   
    Dim wrk As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
   
'  Create DAO workspace and open database within that workspace
    Set wrk = CreateWorkspace("", "Admin", "", dbUseJet)
    Set dbs = wrk.OpenDatabase("C:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb")
   
'  Open a reference to the target table
    Set tdf = dbs.TableDefs("Customers")
   
'  Create a new field object
    Set fld = New DAO.Field
   
'  set the properties for the field
    With fld
        .Name = "StarSign"
        .DefaultValue = "NONE"
        .Type = dbText
        .Size = 25
    End With
   
'  Append the field to the target table and refresh
'  the table's field collection
    With tdf
        .Fields.Append fld
        .Fields.Refresh
    End With

    dbs.Close
    wrk.Close
   
    Set tdf = Nothing
    Set dbs = Nothing
    Set wrk = Nothing

End Function


Good luck

Marcus in Melbourne
Marcus in Melbourne
Thursday, April 14, 2005
 
 
Thanks Marcus - you're right.  Now I remember...and I still have my Access97 Developer's Handbook  :)

hmm...I wonder if I can port this: http://www.buygold.net/v07n01/v07n01.html

to DAO...
MicroISV Guy Send private email
Thursday, April 14, 2005
 
 
You're welcome MicroISV Guy,

I don't see any reason why you couldn't port that functionality to DAO. Two other resources you may be interested in.

SQL Server has the capacity to generate the SQL script required to programmatically create a database using SQL. This is a suitable start to investigate dbs modifications using the DDL side of SQL. Remember though that SQL Server and Access use different dialects of SQL.

Also FMS have some free utilities on their website (fmsinc.com). One of these is called DAO Explorer which is an Access Add-in which lets you view and explore all the database objects (and their properties) vie a treeview control. It's a free download and the source code is avaiable.

Enjoy!
Marcus in Melbourne
Thursday, April 14, 2005
 
 
Hey MicroISV Guy,

If you exploer that site a little further you'll find that this guy has a DAO example to dynamically modifying an Access database. Have a look here:

http://www.buygold.net/v04n02/v04n02.html

The job's half done.
Marcus in Melbourne
Thursday, April 14, 2005
 
 
Hey allright!  Thanks.  Is it generally accepted that there are fewer missing dependency related problems with the general population out there when using DAO vs. ADO ?
MicroISV Guy Send private email
Thursday, April 14, 2005
 
 
There's at least one commercial tool that's focused on automatically updating Access databases, including table schemas, other Access objects, and VBA code:

http://www.databasecreations.com/prod_surgicalstrike.htm
Herbert Sitz Send private email
Friday, April 15, 2005
 
 
I've had pretty good luck keeping a build number in the DB, and checking against build number in the app.  If you detect an older DB you can upgrade (backup first).  Use a DB upgrade script (text file, XML, whatever) that you can walk through cumulatively, and make sure you have build numbers in there, too. 

That way, you can always upgrade any DB version to current by applying all the right (and only the right) DB changes.  There are some issues when you get into more complex upgrades (complex data manipulation, for instance), but in general, this works pretty well, and it will also work incrementally for your internal development and testing (ie, testers and beta users will upgrade correctly).
D. Lambert Send private email
Friday, April 22, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz