Chinese - Simplified   Dutch   English - United States   French   German   Portuguese - Iberian   Spanish  


Usenet Posting #11 - Trigeminal Software, Inc. (English)

Subject: INFO: Replication and GUIDs, the Good, the Bad, and the Ugly
(Originally posted 7/26/99)
Globally Unique Identifiers... or GUIDs (pronounced like Geoduck with the "uck" or like "Gooo-id" for people who can't handle the other sound) are basically unique across time and space. They are used throughout COM, and they are used by Jet replication as a way of creating an ID for records that will stay constant and unique no matter what other values (like key values) may change.

Many people consider the idea of using GUIDs for their primary key/foreign key values, as they will guarantee uniqueness. However, there are many reasons to think twice before going down this road:

1) In an app, a "ReplicationID Autonumber field" (i.e. an autonumber GUID), which is then turned into a replica, Jet will use your column instead of creating an s_Guid column. Unfortunately, the Conflict Resolver Wizard of Access 95 and 97 and the Conflict Viewer of Access 2000 are assuming an s_Guid column and thus conflict resolution will not work properly via the wizard, and you are on your own.

2) A GUID is literally stored as a 16-byte binary value. It also has a canonical (text) form and a DAO/Jet-specific form as well, consisting of {guid {xxx}} where xxx is the "canonical" value. This last format is what Jet will display anytime you do a select statement from a GUID field through DAO... the canonical form will be used if you go through Jet directly (as through Access datasheets) or ADO via the Jet OLE DB provider. In addition, the rules of DAO vs. ADO filters, finds, Access Domain functions, Access filter by form, form/subform links, .Text vs. .Value for controls bound to GUID fields, and all search methods, are very hard to follow and change with every version. Access has built-in functions to help convert between binary and canonical form named StringFromGUID and GUIDFromString, but they unfortunately use the DAO syntax, even though all of COM and most other sources prefer straight canonical. This whole hodgepodge means that attempting to use a GUID field for these operations means you are hitting a moving target.

3) The rules in #2 change with every version and almost universally break what worked the version before. So you essentially have to re-review all of the work when upgrading.

4) Autonumbers are often made visible to users, for better or worse, and this is obviously very impractical for GUIDs, even if all of the myriad of bad issues with searching and such listed above were not actual problems. GUIDs just confuse users and no one wants a GUID to identify themselves.

Now, it is true that all of the issues hinted at in #2 can be worked through using many techniques, including:

1) Experimenting with using the true canonical form vs. the {guid {xxx}} canonical form

2) Conversion use via the two Access functions

3) Using CStr() in the SELECT statement's field values that return GUIDs so you can get the true canonical format

4) Making note of all the places you make some changes so that if/when you upgrade you can revisit all of them (as most will be broken and changed next version).

5) Realizing that the underlying field in the DAO case is a dbGuid type field and the underlying ADO case is an adGuid type field... despite the fact that both return a "text" type field when you are just looking at the field's value helps to underscore the great efforts that they all go to in order to appear flexible... it is the main reason why all of #2 is so hard to predict.

Here (by the way) are the non-Access functions to do Guid/String and String/Guid stuff if you are working from VB. Just paste it into a module, etc.

Type GUID 
    Data1 As Long 
    Data2 As Integer 
    Data3 As Integer 
    Data4(7) As Byte 
End Type 

Private Declare Function _ 
 StringFromGUID2 Lib "ole32.dll" _ 
 (rclsid As GUID, ByVal lpsz As Long, _ 
 ByVal cbMax As Long) As Long 

Private Declare Function _ 
 CLSIDFromString Lib "ole32.dll" _ 
 (pstCLS As Long, clsid As GUID) As Long 

Function GuidFromStGuid(stGuid As String) As GUID 
    Call CLSIDFromString(ByVal StrPtr(stGuid), _ 
End Function 

Function StGuidFromGuid(rclsid As GUID) As String 
    Dim stGuid As String 
    Dim cch As Long 

    ' 39 chars  for the GUID plus room for the Null char 
    stGuid = String$(40, vbNullChar) 
    cch = StringFromGUID2(rclsid, StrPtr(stGuid), 39) 
    StGuidFromGuid = Left$(stGuid, cch) 
End Function 


While it is possible to surmount both the issues in #2 and #3, #2 is very problematic and is very difficult to consider maintainable in light of #3.  It is generally not worth the risks of bugs and issues or in forcing upgrade problems. There are better ways to choose PKs that do not involve such risks.... so it is almost always advisable to go that way if you want to write clean and easily maintainable database and replicated applications.

ReplicaID: {E5D50A9B-33D2-11D3-AAB3-00104BA31425}

Back to Usenet Musings

Problems with this site? Please contact the
with your comments, questions, or suggestions.