April 1995
Moss Micro has been building custom software applications since 1987. The primary development thrust has been in support of the Microsoft suite of applications and tools such as Visual Basic, Word, Excel, Access, and SQL Server. Other database products on Windows NT and UNIX, such as Sybase and Oracle, are supported along with a variety of front-end products and programming languages. The fast-growing staff of Moss Micro, now numbering 27, represents over 85 years of software development experience. You can contact Moss Micro at 31952 Camino Capistrano, Suite C22, San Juan Capistrano, CA 92675. Phone: (714) 248-1701, Fax: (714) 248-0203.
Click here to open or copy the files in the DBSync sample application for this technical article.
This paper is a discussion of a solution to synchronize remote databases using Microsoft® Access®, Microsoft SQL Server, ODBC, and Microsoft Mail.
Sharing data using database applications among multiple users typically requires that the users all be connected to the same data source. In some situations, however, the multiple users (clients) need to have their own copy or "slice" of the data on a remote PC, or are in some other way unable to connect to the main (server) data source because of practical or performance reasons.
For example, a large sales force that is mostly mobile logs a great deal of information about their daily customer visits, changes in a customer's demographic information, additions or deletions to contact information, and so on. However, because these users are remote, these changes are recorded only on their personal machines, not in the master database. This information from the field could be useful to the corporate office, to enhance customer service, track customer activity, and so on. Synchronization would allow remote users the performance and freedom benefit of a stand-alone system, while providing the corporate office with reliable, synchronized data on each of its customers.
This paper (and accompanying sample application files) presents a solution to this problem, using Microsoft® Access® 2.0 as the remote client.
The final solution, "DBSync Contact Manager," was built in about two weeks, within the following parameters:
The DBSync Contact Manager application is intended primarily as a guide and example of the type of problems that can be solved using the Microsoft Office/BackOffice/Language tools. A brief tour of the "features" of the application may help you understand the scope and purpose of some of the development.
With the above considerations in mind, the following components were selected:
As each change is made to the database (in Microsoft Access 2.0), a "transaction" is recorded with the changes. Each transaction records the time, user identification, and type of change. At the end of a "session," (user's interaction with the database) or a menu command, the outstanding "transactions" are packaged into a file and sent (e-mailed) to the SQL Server counterpart. These files are then stored on the local machine until a confirmation is received from the server. The confirmation message is sent (via Microsoft Mail) and the file is deleted from the user's machine. If outstanding files are still on the local machine when a new file is sent, the outstanding (unconfirmed) file is sent first.
On the server side, a single mail client receives the messages. When the message is received the file is "unpacked," and the transactions are applied to the server. Each successfully applied transaction is stored for future distribution to related users. When the changes have been successfully applied, a "log" entry is made in the database to prevent the reapplication of the same changes.
Next, at a regular interval, confirmation messages are sent to users and the "applied" log entry is deleted. If the confirmation fails, the file is sent again from the remote user and the entire process starts again.
The applied changes (transactions) are stored in another table for distribution to other remote users (managers, related users, and so on). Once a day, the transactions are ordered by date and distributed to the related users. When these changes are received (as part of the connection to Mail) they are applied to the client's database. The client sends a similar confirmation message to the server, removing the server's local copy of the transaction file.
There are three types of changes that can be made to data in a database: INSERT (or add), UPDATE (or change), and DELETE. Microsoft Access fires a form level "event" for each of these changes. As each event occurs, the key information is stored in an attached table.
Additionally, the current user (by number) and the CustomerID are also stored for each transaction. This allows the DBSync process to avoid sending the same change back to the user who made the change, and helps build the related user list when distributing the transactions.
On every form that is involved in the DBSync process, you will need to store a form level variable of type DBSYNC_INFO and handle the following events: Form_BeforeUpdate(), Form_AfterUpdate(), Form_Delete(), Form_AfterDelConfirm(), and Form_AfterInsert(). For each of these events you will call dbx_OnRowChange() with a reference to the form (caller) and reference to the DBSYNC_INFO structure.
Almost all of the transaction events happen from a form, but the DBSync code also provides a programmatic method of logging a transaction. For example (from the Customers_datasheet form):
Sub Form_AfterInsert ()
On Error GoTo Form_AfterInsert_Error
Dim rs As Recordset
Dim sSql As String
'log the customer table change
dbxInfo.nStatus = DBSYNC_INSERT
dbx_OnRowChange Me, dbxInfo
'Now add a related row into the UsersCustomers table so that
'this customer will show up in this user's list.
Set rs = db.OpenRecordset("UsersCustomers", DB_OPEN_DYNASET, DB_APPENDONLY)
rs.AddNew
rs("UserID") = tGlob.lUserID
rs("CustomerID") = Me.CustomerID
rs.Update
rs.FindFirst "UserID = " & CStr(tGlob.lUserID) & " and CustomerID = " & CStr(Me.CustomerID)
If (Not (rs.NoMatch)) Then
dbx_LogTransaction rs, DBSYNC_INSERT 'Log the transaction.
Else
DoMessageBox IDS_E_ADDUSERCUSTOMER
End If
Form_AfterInsert_Exit:
If (Not (rs Is Nothing)) Then rs.Close
Set rs = Nothing
Exit Sub
Form_AfterInsert_Error:
GenericError "Customers_datasheet: Form_AfterInsert()", Err
Exit Sub
End Sub
After all the changes have been logged (for the session, or day, or whatever) call dbx_SetTransTableSQL() to prepare the transactions for the synchronization process. In practical terms, this means walking back through the Primary Key names and values for each transaction and building an ODBC-runnable SQL statement that reflects the changes.
In some cases, the row that the SQL statement is being built from will be unavailable. For instance, DELETE changes are simply written to the transaction table without looking for the (deleted) row. Also, because each transaction is logged as it happens, a row may be unavailable because it was first INSERTED, then UPDATED, then DELETED. In this case, the transaction is not sent.
A transaction is recorded for the primary table in the affected record set only. (This is determined by the dbsyncid column in the record set). Because each transaction is recorded against only this one table at a time, it is essential that the SQL server provide the exact functionality of the Jet engine—mainly referential integrity triggers and cascading updates and deletes.
There has been no attempt to "reproduce" the engine level functionality in the DBSync implementation. Instead, the Microsoft Upsizing Wizard was used to make a "duplicate" of the affected database. (See the DBSYNC.SQL script and/or the resulting database for details.)
In a remote system such as this, it becomes important that each user have his or her own "range" of new row identifiers (or PrimaryKey values). Typically, an Access "counter" column is used, but in this case, the chance for duplicates is certain. The solution is to give each user a "range" of new IDs, based on his or her user ID. In the DBSync Contact Manager, a function called GetNewID() is used to give each user a range of 1 million new IDs in any one table. (Note that this limits the number of users to about 2,000.)
Before each insert event, GetNewID() is called and the return value is assigned to the appropriate column. For example (from the Customers_datasheet form):
Sub Form_BeforeUpdate (Cancel As Integer)
Me.DateModified = Now
If (IsNull(Me.CustomerID)) Then
Me.CustomerID = GetNewID(ID_TABLE_CUSTOMERS, "CustomerID")
End If
dbxInfo.nStatus = DBSYNC_BEFORE_UPDATE
dbx_OnRowChange Me, dbxInfo
End Sub
A custom message is a special class or type of message you can create to exchange information between Mail recipients or Mail-aware applications. The DBSYNC.DLL processes three types of custom messages: DBSync, DBSyncToCorp, and DBSyncConfirm. When a message of any one of these types arrives in the in-box, Mail calls a single function in DBSYNC.DLL, Command().
After the Command() function is called, DBSYNC.DLL establishes a session with Mail (by logging on), reads the custom message, saves the attached file, and, using ODBC, applies the SQL statements from the file to the appropriate data source. (For details, see the DBSYNC.DLL source files.) If the file is processed without error, the message is deleted, with no user intervention. If the message is a DBSyncConfirm (or confirmation message) the message body contains a file name (prefaced by a user ID if it is sent by a remote user). When the message is processed, DBSYNC.DLL looks in the DBSYNC.INI file for the path to the local storage of .DBX (transaction) files. If the file exists, it is deleted. This prevents the file from being sent an additional time.
The custom message types need to be registered in the remote user's MSMAIL.INI file, as shown in the following code example. (Setup will create these settings; see the SETUP.TXT for details.)
IPM.DBSync.DBSync=3.0;;;;<system>dbsync.dll;;2122222100000000;;;;
IPM.DBSync.DBSyncToCorp=3.0;;;;<system>dbsync.dll;;2122222100000000;;;;
IPM.DBSync.DBSyncConfirm=3.0;;;;<system>dbsync.dll;;2122222100000000;;;;
Strictly speaking, only two of these settings are required, depending on what type of database you are using. For remote users, only the first and third are required. For an SQL Server Mail client (there should only be one), only the second and third lines are required. In a production environment, you should avoid the second message declaration on the remote users' machines.
The series of numbers (16) indicates when the custom .DLL is to be called. In most cases, the 8th position, which represents a delivery operation (a message of this class has arrived in the in-box) is all that will be acted upon by the DBSync process. However, in case of unexpected failure, the second position, which represents an open operation (a user double-clicks on the message) also causes Mail to call the custom .DLL and process the message. This can be useful if you are testing the process.
For more information, you may want to read the Microsoft Mail technical reference.