Last Update: 2024 - 01 - 25
New Bug in Microsoft Access 365 Version 2205 - SQL Server NVarchar Primary Keys cause #DELETED when linked with ODBC
by Philipp Stiefel, originally published 2022-05-26, last updated 2022-06-02
On May 26, 2022, I was made aware of a new bug in Microsoft Access 365 Version 2205. The exact Access version I reproduced the bug in is: Version 2205 Build 16.0.15225.20028 – 32-bit. I did not try reproducing the issue in other builds of Access V2205, but I assume it is reproducible in other builds, including the 64bit edition, as well.
Please note: This article is only about the very specific scenario described below. If you see #Deleted in an older version of Microsoft Access or independently of an Nvarchar primary key, the cause will be something different.
Steps to Reproduce
1. Create a table in a SQL Server database which has an Nvarchar column as primary key and insert at least one record. E.g.:
CREATE TABLE TestNVC ( NVCPK Nvarchar(100) NOT NULL PRIMARY KEY, Dummy Varchar(100) NULL); GO INSERT INTO TestNVC (NVCPK, Dummy) VALUES ('ABC', 'Test 1'); GO
2. Link the table into a Microsoft Access database using the “SQL Server” ODBC driver (I used version 6.03.9600.17415). (The issue is also reproducible with other ODBC drivers. See the list in the “Additional Info 2” section below.)
3. Open the linked table by double clicking the table in the Navigation Pane.
4. Observe: All columns in all records display #Deleted
Additional Info 1:
The problem is related to the way Microsoft Access queries the records in dynaset recordsets. It first retrieves the primary key and then uses each pk value to fetch the remaining columns of each record. The latter part is what fails and results in “#Deleted” being displayed instead of the actual data.
If you do not need an updateable recordset from the linked table, you could create a query in Access creating a Snapshot type recordset. Snapshot recordset are not affected by this issue.
(If you want to know more about how Access works with ODBC linked tables, check out my text Microsoft Access - ODBC Linked Tables – Mechanisms and Performance.)
Here is SQL Server Profiler trace, which shows that instead of the actual primary key values there are unrelated Unicode characters sent to the server:
If you want to know more about using SQL Server Profiler, particularly when diagnosing issues with Microsoft Access client server application, I suggest you watch my video on the topic: Why (still) use SQL Server Profiler?
Additional Info 2 / Workaround:
The problem does not happen when the table is linked into the Access database using the “ODBC Driver 17 for SQL Server” (I tested version 2017.176.01.01.).
I did a quick test of the other ODBC drives for SQL Server I’ve got installed here.
Relinking the SQL Server tables using the “ODBC Driver 17 for SQL Server”, or another non-affected ODBC driver is my recommendation to work around the issue. Installing a different driver and relinking the tables can be done without any changes to the code or design of an affected Access application.
Another option to deal with this issue is to revert to a previous version of Microsoft Office 365.
MySQL and PostgreSQL also affected
I’ve read a couple of public posts this week where people report that they are also affected by this bug using a MySQL or PostgreSQL backend database.
A quick test appears to confirm the issue. I can reproduce the problem with a MySQL database using the MySQL ODBC 8.0 Unicode Driver.
The MySQL ODBC 8.0 ANSI Driver seems not to be affected. Unfortunately, using this unaffected driver is not a workaround in most scenarios as you probably need Unicode support when you were using that ODBC driver.
I have no installation of PostgreSQL available in my local development environment to try to reproduce the issue with that database server. However, as we’ve now seen a wide variety of ODBC drivers being affected by issue, it is very likely that applications using other DMBS, such as PostgreSQL, are also suffering from this bug.
A Bug Fix by Microsoft is coming!
There is still no official info from Microsoft on this whole issue that I’m aware of.
However, I received info from a contact of mine that this issue is fixed on Microsoft’s end and that a bug fix should become publicly available as new version 2206 on Thursday or Friday this week (2022-06-02 / 03) for the current (monthly) channel of Microsoft 365.
As of 2022-06-02 19:30 UTC this update was not yet available to me.
I will never share your email with anyone. You can unsubscribe any time.
© 1999 - 2024 by Philipp Stiefel - Privacy Policiy