Access Replication

Replication in Access provides a method of allowing remote users to work with a local copy of the database while disconnected from the network, and then synchronizing their changes when they do connect. This feature has been part of Access since the 90's but sadly, although Access 2007 supports it if you stay with the .mdb file format, if you choose to convert to the new .accdb format this feature goes away (along with WorkGroup Security).

The idea of replication is that you create a Design Master (DM) that adds additional hidden system fields which Access uses to track record changes that need to be replicated. You also create a Replica which has all the programming and data of the DM but without any design options, thus protecting the design from unauthorized changes. During the sync process, data is exchanged between the DM and replica, and design changes made to the DM are also propagated to the replica.

The replication model is sensitive to the drive, path, and file name; moving the DM will cause it to become a replica; copying the DM will result in creating another replica.

In addition to the fields added to each table, there are also a number of hidden system tables created when you replicate a database; the net result is that the DM and the replicas are considerably larger than the original non-replicated file. For example, a 27 MB file can grow to 44 MB when replicated. Once the users begin working with the file and syncing, that file can grow to 48-59 MB (these numbers are taken from an actual production system). For that reason, it is strongly recommended that you enable the Compact on Close option to keep the file as small as possible under the circumstances.

It is important to note that creating a replica will change AutoNumber fields from Increment to Random. This is necessary to prevent conflicts - for example, if user A added a new record, and user B added a new record, Increment would otherwise result in a key violation when attempting to sync. Therefore, if the app relies on a sequential series of new numbers, e.g. invoice numbers, you will need to develop a different strategy for that numbering system.

During the sync process, any conflicts will result in a hidden table being created, e.g. if a table tblOrders has sync conflicts then Access will create a tblOrders_Conflict table that holds the GUIDS for the "winning" and "losing" replica, table, and field.

It should be noted that simply deleting old records will not necessarily reduce the file size; Access maintains a system table, MSysTombstone, which retains a pointer to every deleted record. Unless you utilize Replication Manager (from the Office 2000 developer kit) or a third-party utility, the default retention period is 1000 days, so all those deleted records will be adding to the size of the file with little real benefit.

A common technique of dealing with the bloat of replication is to rebuild the master file periodically. To accomplish this, you first have all users sync & then delete their local copy, then you would need to create a new empty file, export all the Access objects into the new file, and also create new tables in the new file that do not have the replication fields & export the data into those new tables. (It is not possible to delete the replication fields from the tables because they are system fields.) The final steps would be to convert that new file to a design master & create a replica, and then distribute that replica to the database users.