Removing Access Replication

Using replication with MS-Access adds numerous system fields and tables, and combined with the replication-tracking system, a replicated Access database can grow to an unwieldy size, especially if you have many users and/or there are frequent changes in the design master.

The customary solution is to rebuild the database, i.e. to create a new non-replicated file and then use that to create a new design master and the replicas.

Unfortunately, you cannot simply create a new empty file and import all your Access objects, because all the system fields in the tables will also be carried over via the import. You cannot simply go into design view & delete those replication fields because they are system fields.

The basic process is to create a new empty file, and then export all the Access objects except the tables, and then export the tables but without the replication fields.

Import the VBA module Unreplicate_Access into your design master, and when you execute the code it will create a new non-replicated version of your database. It will also set a number of startup properties such as hiding the DB Window, turning off shortcut keys and so on.

Once you have created the new file, simply copy it over your existing design master (after you make a backup copy, of course) and then click Tools ~ Replication ~ Create a Replica and follow the prompts.

This module is still a work in progress; it does not set any primary keys, nor does it copy any default values for the tables. This will appear in a future post. But there won't be any code to create relationships; Microsoft actually recommends against using RI in a replicated db due to the mechanics of replication.