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.
8.21.1. 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 in the local database. Why is a table kept with the modified dates? The reason is a safeguard: users might alter the tables locally, thereby changing the last modified date on local tables. The point is to check if the master database contains updated tables that have not been used yet. The dates in the local tblTableVersions table are the modified dates of tables in the master databasefrom the last time any particular table was copied.
Figure 8-37 shows the tblTableVersions table. Two tables have dates that are in December 2004.
Figure 8-37. Keeping track of the last modified date
8.21.2. The Code
When the local database opens, the AutoExec macro calls the following get_ updates function, which therefore runs upon startup:
Function get_updates() On Error GoTo err_end Dim update_db As String update_db = "G:\UpdateDB.mdb" Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim local_tbl As String Dim current_object_date As Date ' Open the catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & update_db For Each tbl In cat.Tables If Left(tbl.Name, 4) <> "MSys" Then current_object_date = _ DLookup("[ModifiedDate]", "tblTableVersions", _ "[TableName] = '" & tbl.Name & "'") If tbl.DateModified > current_object_date Then DoCmd.DeleteObject acTable, tbl.Name DoCmd.TransferDatabase acImport, "Microsoft Access", _ update_db, acTable, tbl.Name, tbl.Name 'store new date conn.Execute ("Update tblTableVersions Set ModifiedDate=#" & _ tbl.DateModified & "# Where TableName='" & tbl.Name & "'") End If End If Next Set cat = Nothing Set conn = Nothing MsgBox "done" Exit Function err_end: MsgBox Err.Description End Function
8.21.3. Running the Code
A mixture of VBA, ADOX, and ADO works together to check for updates. An ADOX catalog object is created and is set to the master databaseG:\ UpdateDB.mdb in this example. All the tables in the master database are examined; however, system tables aren't included. All system tables start with MSys [Hack #15].
The DateModified property of each table in the master database is checked against the locally stored date for the same named tables. When it finds that a master database table has a newer modified date, DoCmd deletes the table in the local database and imports the new table from the master database. Then, a SQL Update statement updates the date for the table in the tblTable-Names table.
After the routine completes, the user can go about her business as usual. The application works as always because the table names have not changed.
The only caveat with this hack is that it is useful to update tables that don't share in a relationship. Related tables need to have the relationship broken before you delete and then reestablish them. Therefore, this hack is perfect for tables that contain the source for lookup lists or that don't participate in a relationship. You can develop additional code to test for relationships and handle them appropriately.