Remove replication from a MS Access database
This VBA function copies or imports all the objects and database startup properties from a Microsoft Access replicated database into an un-replicated database. It removes tablename_Conflict tables and removes replication -related fields like s_GUID, etc.
The Microsoft support site describes some methods that can be used change a replicated MS Access database to a normal database:
Access 95, Access 97 wizards and manual method description –
Access 2000 – Access 2003 manual method –
Not only do the tables need to recreated, but there is a list of other items to restore:
- Non-default references (in code)
- Security of the database objects
- Table indexes and relationships
- Table properties
- Validation rules
- Database properties (like the startup options )
- Forms, Reports, Modules, Macros, and Pages
Other than the non-default references, database object security, and the import of Pages, the code below automatically takes care of the whole list.
- Create a blank new database. In my version of Windows, I just did a right-click in the folder, selected the New option, then Microsoft Office Access Database. As soon as the file appears, name it something useful.
- The code is meant to run in a database separate from the replicated or new database. Import the code module (which you can download below) or create a new code module and copy the code into it.
- In the database with the code module, use the Tools, References menu option to set some references. The FileDialog object requires a reference to a Microsoft Office Object Library (10 [Access 2002] or later). And because the code uses DOA, it requires a reference to Microsoft DAO Object Library, or for Access
2007 and later, the Microsoft Office Access database engine Object Library.
- Run the UnReplicate() function.
- When the first browser box appears, select the replicated database. When the second browser box appears, select the new database.
- The hourglass will appear, and depending upon how much data is in your tables and the speed of your processor, it may take several minutes before the “UnReplicate is complete” message appears.
- If the new database has any code modules, set non-default dll references by using the Tools, References menu option. When you get them all properly selected, you should be able to compile without an error.
- If you need to establish database object security, finish the job with that.
Notes about the code:
- The code works best when it is called from a database separate from the replicated and new databases. This is because the TransferDatabase actions that are used to import the Forms, Reports, etc. require the new database to be opened exclusively. That is also why the dbNew database is closed before the TransferDatabase actions are called using the appNew object.
- Because of the reference to the Microsoft Office Object Library, the file picker part of the code will only work if Access 2002 or later is installed. You can replace this with some common dialog code for versions before that.
- The database properties that are copied over relate to a select list of startup options. You can find the list where the avarSUOpt array is created. Feel free to add or subtract from the list.
- If you need to import Pages as well, add a section similar to the Import Macros section and use the “Pages” container.