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), _
GuidFromStGuid)
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
FINAL RECOMMENDATION:
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.
MichKa
