How to Split a Replicated Access 97 Database
By Jeffrey Streutker, MCSE
Those who are inexperienced Access database developers are often unaware that replication of changes to the design of objects other than data tables is known to cause problems. This is disappointing since it at first seems that replication is the best way to distribute the complete Access application, and, after all, Microsoft supports it.
The problem is that Jet replication was designed with just data tables in mind. This is because Jet replication was intended mainly for Visual Basic applications where only the data would be stored in a *.mdb file. This is where it was tested the most. As far as Access 97 was concerned, it was always understood that a good developer would have already had a split database before converting it into a replica so that only the tables would be replicated. For an introduction on the concept of splitting an Access database, see Chapter
15, Section 3, of the "Building Applications in Microsoft Access 97" document that comes on the Access 97 CD.
Without splitting a database before replicating, you will find that your replicas will "bloat" in size. This is because every single change you make to a form or module, for instance, gets placed in a hidden table, and thus becomes information that needs to be replicated. Imagine for a moment that you made 30 attempts at making a moduleís code work correctly. Each of these changes (all but the last one are unnecessary) gets sent to all replicas. In practice, developers who failed to split before replicating experienced an annoying problem: after a while (maybe even months) of replication working correctly, suddenly a module would fail to work, or a form could not be opened.
So how can you split a database that has already been replicated? Here I suggest the following steps to help.
Note: If you want to follow the exact naming convention that Microsoft uses in its Split Database Wizard, then you will have to rename your backend replica by appending "_be" to its file name. The safest way to do this is within Replication Manager with the Move Replica command.
- Do not use the Split Database Wizard. This is because it splits a database in precisely the wrong way so that your data tables become no longer replicable Ė it moves the tables to a new file and keeps the rest of the objects within the replica. Instead, what we need to do is keep the data tables within the replica, and move all the other objects into a new, unreplicated database file. That way the back end remains replicable as part of the current replica set, and the front end is now a standalone database, which can simply be copied to every application user.
- Make sure you have all your replicas synchronized so that you know you have up to date data, just in case you corrupt the Design Master.
- Create a new Access database file, which will be the front end. Following the Microsoft example, you can append "_fe" to the original database name, although it really doesnít matter what you call it.
- Having this new file open, import all the queries, forms, reports, macros, and modules from the Design Master. If you have a switchboard in place, which you generated with the Switchboard Manager, then you also need to import the Switchboard Items table.
- Create links to the Design Masterís tables.
- If you have implemented database security, then you will have lost the permission settings in all the objects that you imported or linked in steps 4 and 5. Therefore, open the Design Master and copy these permission settings to the new file (i.e., the front end).
- Now you need to add code to the front end that refreshes the links to the userís local replica. You will find help in doing this in the Developer Solutions database, which comes as a sample database with Access 97. Select "Link tables at startup" from the "Use Multiple Databases" category.
- Distribute the front end to all your users. Remember that this file should never be replicated. You simply make copies for anyone who needs it. Tell your users to switch to using the front end. When you are assured that they are now using the front end, you can go ahead and convert the Design Master to a back end (next two steps) so that the next time they synchronize, their replicas will also become back ends.
- Open the Design Master, and delete all the objects that you imported in step 4.
- Compact the Design Master twice, then synchronize it with all your other replicas.
Jeffrey Streutker is an IT Manager for a HVAC (Heating, Ventilation, and Air Conditioning) Controls company in the Toronto area. He is an electronics technologist who has also studied computer science and mathematics at the University of Waterloo, Canada, and has periodically done programming work since 1985 in many different languages. His current project is converting an extensive Lotus Notes-based database system to Access 97, including replication, in order to support technicians in the field with service-tracking and purchase order generation databases, and master address book with fax client/server support.
Return to Resources