Access reports "Record is deleted" for a GUID key
In a linked table whose key is of type uniqueidentifier, the datasheet shows #Deleted in every
row, or changing and deleting fail with error 3167 “Record is deleted”. A form opened with a
condition on the GUID stays empty; a subform linked through the GUID shows no records. Filtering,
counting, and DLookup over the same table, by contrast, return correct results.
Environment
Access (ACCDB, ACCDE, and Access Runtime) with a driver older than version 0.6.0. Tables with an integer or text key aren't affected.
Cause
Access reloads the records of a linked table by their key. To do so, it reads the key twice – plain
and with the full name (SELECT "Id", "dbo"."Tabelle"."Id") – and builds its bookmarks from the
second column. Older driver states filled only the first of a column selected twice, and they
returned the GUID to Access, which registers as an ODBC 2 application, not as text. As a result,
Access considered every key empty, queried WHERE "Id" IS NULL, and found no record.
Resolution
- Install the current version of the driver in the bitness of Office (see Deploy the driver).
- Close Access and reopen the database; the links don't have to be recreated.
Verification
Open the linked table in Datasheet view: the records appear with their values. Change a value and leave the record – it's saved; a change by someone else in between is reported by Access as a write conflict (3197).