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.