Hack #91. Automatically Check for Database Table Updates
Pull updated objects from a master database when your database opens.
One of the issues you face with a distributed application is how to propagate updated tables to the user community. This happens when you must process new data or when lookup lists have new values that have table data as the source. Redistributing the entire database is one way to go, although that disrupts the workflow. In that scenario, users must stop what they are doing, get the new file, and save it somewhere. Even when that process is automated, you can't be sure someone is currently using the database you are about to overwrite.
Instead, here's a great way to have users' applications update themselves. The update occurs when a user starts up her local copy of the database. A code routine checks the database's tables against those in a master database on the network. When a table in the master database is found to be newer, it is copied into the user's database.
Running Code at Startup
Each user's locally installed database contains a table named tblTableVersions that has two fields: one contains the names of the tables in the database, and the other has the last modified date of each table. When the database is opened, a code routine opens the master database and compares the modified date of the tables in the master database with the records in the tblTableVersions table.
You might wonder why we don't just compare the modified dates of the tables themselves ...
Get Access Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
