Navigation:  Bronze Collection > Dec-1998 > Access Replication

Mary Chipman          Bronze Collection   

 

GARRY's NOTE:  Access Replication works on MDB files.  It was phased out for ACCDB files in Access 2007 but still works in the MDB files in that version (and probably phased out in 2010)

 
Mary Chipman draws on her expertise with replication to answer a variety of questions from a number of sources. Mary tackles using GUIDs, deleting replicas, replicating multiple database applications, and other thorny issues.
 
I converted a database into a replicated database, and my Autonumber fields now contain GUIDs. Since these fields are unique across all the copies of the database, I'd like to use them in my applications. Is there a way to use GUID as a criterion for a DLookup or for locating records in some other way?
 
GUIDs are stored as byte arrays, not regular strings or numbers, so Access can't return data from a field or control that holds a GUID by using the usual methods. To handle this, Access provides the StringFromGUID function that you can use to retrieve the value of a GUID and translate it into a usable string. The following example uses the DLookup function to find the record in the Category table where the CategoryName field contains "Beverages" and retrieve the CategoryID field from the record. Since the CategoryID field is a replication-type field like the one you describe, it contains a GUID, so the example uses the StringFromGUID function to convert the function's result to a string:
 

 
 
This works fine for a simple DLookup, but if you want to do something more complex, such as using a combo box with GUIDs, what do you do? This gets a little trickier. In this example, I'll assume that the application needs code in the AfterUpdate event of a combo box. In the following example, the field [PK] is the Primary Key of the table the form is bound to, and it's also a GUID:
 

 
 
The preceding code would work just fine -- if PK weren't a GUID. However, in this case, when the AfterUpdate code runs, you'll receive runtime error 3614 -- "GUID not allowed in Find method criteria expression." Oops. The logical place to look for help is, of course, Microsoft Access Online Help. However, you're going to come up empty if you're looking for a useful example -- the only example (under the StringFromGUID topic) just shows you how to print the return value from StringFromGUID() to the Debug window, which is useless. With thanks to Steve Thompson for digging into this to come up with a useful example, here's how you can use the results of a combo box tied to a GUID field:
 

 
 
Another way to look at the StringFromGUID() function is that it converts a GUID byte array into a string that can be matched against another GUID converted by StringFromGUID. Building on this, the preceding code sample has the FindFirst criteria use StringFromGUID to convert the PK field in the table to a string. The result is that the FindFirst compares the converted GUID from the combo box against converted GUID fields from the table. And this does work.
 
If you want to convert the string back to a GUID, you can use the GUIDFromString() function. It's not apparent that there's any need for GUIDFromString() in working with Access data. However, the use of GUIDs isn't restricted to replication, or even to Access. GUIDs are used in Microsoft's fundamental technology for creating objects to uniquely identify every object in the Windows universe, for instance.
 
Despite that, both StringFromGUID and GUIDFromString are Access functions and not part of either DAO or VBA. So if you're working with replicated data in Visual Basic, you won't be able to use these functions -- VBA doesn't supply any equivalent functions. If you need these functions, you'll have to use your own StringFromGUID and GUIDFromString functions, using API calls to OLE32.dll. If you do use replacement functions, you might want to consider using them in your Access applications as well. Doing this will make your code portable to any application that uses VBA and independent of Access. Michael Kaplan kindly provided the following samples of the replacement functions:
 

 
 
Everyone told me to split my database into two files: a front-end MDB that contains my tables and another back-end MDB that contains my forms, reports, and so forth. So I did. Now I need to replicate my database. What's the best way to implement replication in this situation?
 
Your best bet is to replicate only the data. Although Jet replication supports synchronizing design changes, in practice this causes as many problems as it solves. There have been reported instances of design changes to forms and modules not propagating successfully to all replicas in a replica set, for instance. If possible, develop your application fully before replicating and distributing it. If you need to make design changes later, then distribute the front-end database separately from the table's database. Since your front-end database contains no data, it should be relatively easy to swap in a new version of it.
 
After setting up my replicated databases, I've discovered I don't need it anymore. I've deleted the file, but it won't go away in the Replication Manager. How do I delete a replica?
 
The Replication Manager doesn't have ESP and doesn't realize that you deleted the replica. You need to synchronize with the missing file. Once the Replication Manager sees that the database file isn't there, Replication Manager will remove the database from the managed list of replicas. If the folder that contained the replica has been deleted or renamed, then you'll need to recreate the folder with its original name (otherwise, the Replication Manager will think that the database is still there and it's just the directory that's moved).
 
I installed Internet Explorer 4.0 on my computer, and now I can't replicate my Access 95 databases.
 
Unfortunately, there's no happy solution to this problem. IE 4 and Access 95 are simply incompatible. You might want to consider upgrading to Access 97, where the problem doesn't exist. There are other benefits to upgrading -- you can take advantage of enhanced replication capabilities, such as partial replication, Internet replication, and indirect synchronizations.
 
The person who set up our replication system has left, and there's very little documentation left behind. I know that some of our databases are full replicas and others are partial replicas. How can I tell which databases are only partial replicas?
 
The information you seek is stored in the ReplicaType field of the systems table, MSysReplicas. A partial replica will have 11 in the ReplicaType field, while a regular replica will have a ReplicaType set to 0. You can also find out whether a replica is a Design Master by checking to see whether the value of the database's ReplicaID property equals the value of its DesignMasterID property.
 
Just to make it a little easier to do the checking, the following function returns a string indicating whether the current database is a Design Master, regular replica, partial replica, or not a replica at all:
 

 
 
We're distributing some reference information from our head office to branch offices. Each branch should only get their information, and, more importantly, they shouldn't be able to change it! How can I create a read-only partial replica?
 
If you use the Partial Replica Wizard to create your partial replica, there's no option to make the replica read-only. However, if you create the replica using VBA code, you can make the database read-only at the same time you create your partial replica database. You can then go back into the Partial Replica Wizard to create the filter and populate the partial replica. Here are the steps you need to take, along with the necessary code to create a read-only partial replica:
 
1. Run the following function from the Design Master. This creates a read-only, partial replica database and returns True or False, depending on whether it was successful or not:
 

 
 
You must pass the function the fully qualified path for the new replica. This call to the function creates a partial replica called "C:\My Documents\PartDB.MDB."
 

 
 
2. Load the Partial Replica Wizard from the Tools, Add-ins menu. If you don't have the Partial Replica Wizard, you can download it from Microsoft's Web site.
 
3. Select "Modify an existing partial replica" from the opening dialog box, and click the Browse button on the following dialog box to locate the partial replica you just created. You'll get a message stating that the partial replica and the full replica's schemas are out of synch. Ignore this message and click Yes. A second dialog box will then appear -- ignore this message also and click Yes again.
 
4. Fill in the filter options as shown in Figure 1.
 
5. Use the Wizard to designate any tables that your filter doesn't directly apply to and that you don't want included in the partial replica.
 
6. Optionally create a report and click the Finish button. Your new replica is now ready to use and can't be modified.
 
If you don't want to use the Partial Replica Wizard to create the replica filter and populate the replica, you can always write code using the ReplicaFilter property and the PopulatePartial method to do so. The following is an abbreviated sample illustrating the syntax needed:
 

 
Figure 1

 
I have a replica that's become quite large. How do I get rid of the junk inside and shrink it down to a reasonable size?
 
When you create your replica set, a default retention period is established for your replicas. The retention period is the number of days that a replica set retains information regarding deleted records, schema changes, and other system-specific information. If you created your replica using the Access menu commands or by dragging it into the Briefcase, the retention period for the replica is set to 1,000 days, which is quite a long time for your replicas to bloat up. If you created your replica using DAO or the Replication Manager, the retention period is 60 days. The only way to modify this value is by opening the Design Master in the Replication Manager and changing the value -- it can't be changed from the Access user interface or from DAO code.
 

Figure 2

 
The following procedure allows you to clean out any existing bloat and then set the retention period to some rational period:
 
1. Synchronize with all of the replicas in the replica set.
 
2. Open the Design Master in the Replication Manager and change the retention period to five days, as shown in Figure 2. Setting it to one would be optimal, but the Replication Manager won't let you set it to anything less than five.
 
3. Compact the Design Master twice, then synchronize with the other replicas. Compact all the replicas twice after synchronization. At this point, your Design Master and replica have had all of their bloat removed.
 
4. Open the Design Master in the Replication Manager and change the retention period to some reasonable time that works for you -- 1,000 days is probably too long, and 60 days might be too short.
 
5. Synchronize with all the replicas again to let them know the new setting. Your replicas should now stay at a reasonable size.
 
 
 
Your download file is called DEEPSA9812AA.ZIP  in the file SA199812_down.zip

This is found in the Bronze Collection on this page
 
 
Sidebar: Foundation Concepts

 
Access lets you create a "replica set." Databases in a replica set can exchange data between themselves when triggered either through code or through the user interface. Each replica set contains a Design Master, which not only replicates its data but also any changes to table structure, form design, and so on. A database in a replica set can also be a "partial replica," which means that it accepts only some of the changes made to other databases (that is, only changes to records that meet some criteria). You create a replica set by converting an ordinary database into a Design Master and then generating other members of the replica from the Design Master. Creating a Design Master and additional members of the replica set can be done from the user interface or from code.
 
Every table in a database that's part of a replica set has some additional fields assigned to it. One of these fields will contain a replication ID. Like an AutoNumber field, this field's contents are generated automatically by Access. This field contains a GUID, a random number from somewhere between 0 and 4 billion. Presumably, every record in every database in the replica set will have a unique GUID in its replication ID field.