How to: Use GUID keys

Bind WklGrid, WklTreeList, WklTreeView, and WklGantt to tables whose key and parent key are GUIDs (SQL Server uniqueidentifier, Access Replication ID), including tables linked through ODBC.

Requires WKL Forms Object Library 1.38.1 or later.

• Nothing to configure. KeyField, ParentField, LinkChildFields and EditKeyFields name GUID fields like any other field; the controls recognize a GUID field by its type.
• Keys in VBA are text. VBA has no GUID type. A key that the control returns (SelectedKey, or the Key argument of an event) is the text Access itself uses for a GUID, {guid {11111111-1111-1111-1111-111111111111}}, which is the same text DAO returns for the field. You can compare it with a field value and insert it into a criteria string as is.
• Keys from VBA in any common format. SelectedKey, NodeText(Key), and the other members that take a key accept {guid {…}}, {…}, and the bare 8-4-4-4-12 digits.
• DLookup is the exception. On a GUID field, DLookup returns the 16 raw bytes as an 8-character string. StringFromGUID converts only a Byte() array and returns such a string unchanged, so let the database do the conversion instead: DLookup("StringFromGUID([Id])", ...).

Select a customer whose GUID key you read with DLookup:

Dim key As String
key = DLookup("StringFromGUID([Id])", "tblCustomer", "Name = 'Conrad'")   ' {guid {33333333-...}}
Me!Grid1.Object.SelectedKey = key

Use the selected key in a criteria string, for example to filter a subform:

Private Sub Grid1_RowSelect(ByVal Key As Variant)
    Me!sfOrders.Form.Filter = "CustomerId = " & Key       ' Key is {guid {...}}
    Me!sfOrders.Form.FilterOn = True
End Sub

A tree over a table with a GUID key and a GUID parent key:

With Me!Tree1.Object
    .RecordSource = "tblFolder"
    .KeyField = "Id"
    .ParentField = "ParentId"      ' Null for a root
    .TextField = "Name"
End With

• For SQL Server tables linked through ODBC Driver 18 or the Wkl OData driver, DAO returns a GUID field as {guid {…}} (field type 15, dbGUID), while DLookup returns the raw bytes as a string.
• Wkl OData: a filter on a GUID column requires a data service that understands the OData v4 GUID literal; a service that doesn't responds with an error such as “Die Filterangabe ist ungueltig” (the filter is invalid). The Wkl OData driver itself sends the correct literal.
• Trees over Wkl OData: the statement that finds the root nodes compares two columns (parent key and key), which the Wkl OData driver doesn't support. The tree therefore reads all records once and finds the root nodes itself (for any key type), so large trees over Wkl OData load completely the first time they're displayed.
• Writing over Wkl OData: the table needs a row version column, whatever the key type. Without one, writes are rejected; see Editing and saving: prerequisites.
• Copies: a record copied in a tree (by holding down Ctrl while dragging) gets a new GUID as its key.

• Editing and saving: prerequisites
• How to: Link to a master record
• How to: Use a composite key