How to enforce Primary Key Uniqueness on a ADODB.Recordset created from scratch?

Question

I am using a recordset as a vehicle for some data that I am acquiring. I am getting duplicates and I was curious as to how to get the recordset to complain if an attempt to add a duplicate is made.

So, essentially I want to create a key field. I have found the adFldKeyColumn parameter but it is not enforced, I must be missing something.

Function CreateIndexedRecordSet() As ADODB.Recordset
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Fields.Append "Name", adBSTR, 255
    rs.Fields.Append "pkey", adInteger, , adFldKeyColumn
    rs.Open

    rs.AddNew Array("Name", "pkey"), Array("foo", 1)
    rs.AddNew Array("Name", "pkey"), Array("bar", 1)  '<--- this should complain

    Debug.Print rs.Supports(CursorOptionEnum.adIndex) '<--- sadly prints False, perhaps use a different provider?

    Set CreateIndexedRecordSet = rs
End Function

Please no triage answers, I know full well I can use a Dictionary whilst I acquire the data and catch duplicates that way, that is what I will do in the meantime. It's just there must be an ADO expert out there who knows the trick.

UPDATE: I have found a Supports method on the RecordSet object, if I pass in CursorOptionEnum.adIndex then it replies False. Perhaps use a different provider?

I think the `adFldKeyColumn` is simply ignored here, a unique constraint is a feature of an index and a recordset only has one of those if its based on an actual indexed table from a database (which is where the index/logic actually exists) — Alex K., Sep 18 '18 at 12:05
@AlexK. yeah, i know the concept is based off a real database index; that is what I am trying to synthesise. Presumably when a recordset generated from SQL Server gets disconnected the indexing is still enforced so the provider (perhaps) is doing some enforcement of its own. Or, has the provider set some magic property intrinsic to ADO that we have yet to discover? — S Meaden, Sep 18 '18 at 12:10
On your update, `adIndex` refers to setting the `Recordset.Index` property to change which index is used when executing the `Recordset.Seek` method. It's unrelated to constraints and enforcing uniqueness — Erik A, Sep 18 '18 at 13:17

Erik A · Accepted Answer · 2018-09-18T13:31:41.967

This can't be achieved, as noted by @AlexK

ADODB recordsets don't support constraints of itself. It's the database that enforces constraints, not the recordset. If your recordset is not updating to a database, there are no constraints.

To demonstrate this behavior, you can test the following in Access:

Create a table named Table1 with 2 fields (ID, primary key, Field1, string)

Run the following code:

Dim rs As New ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
conn.CursorLocation = adUseClient
rs.Open "SELECT * FROM Table1", conn, adOpenKeyset, adLockBatchOptimistic 'Open up recordset
Set rs.ActiveConnection = Nothing 'Disconnect it from the database connection
rs.AddNew Array("ID", "Field1"), Array(1, "A") 'Add a record
rs.AddNew Array("ID", "Field1"), Array(1, "A") 'And another identical one
Set rs.ActiveConnection = conn
'All fine until here, recordset contains 2 records with identical primary key
rs.UpdateBatch 'Errors, duplicate primary key

Of course, you can manually check for duplicates yourself, or set a handler on the Recordset_WillChangeRecord event to enforce constraints, but you said you weren't interested in that.

_{can't be done answers are allowed per Is “this is not possible” an acceptable answer?}

How to enforce Primary Key Uniqueness on a ADODB.Recordset created from scratch?

1 Answers1