Language: Deutsch English

Home

Über mich

MS Access

Access & Email

Access-Client-Server

mySQL & Access

Visual Basic
for Applications

ASP

Random dumps of
dubious wisdom

Downloads

Windows Tips

Links

Impressum / Kontakt

Last Update: 2024 - 05 - 21

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

#Deleted displayed in records of an Microsoft Access ODBC linked table

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:

SQL Server Profiler displays the incorrect SQL causing the problem in Microsoft Access

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.).

Driver list in ODBC admin dialog showing affectd ODBC driver and unaffected driver

I did a quick test of the other ODBC drives for SQL Server I’ve got installed here.

SQL Server – Affected by this issue
SQL Server Native Client 10.0 – Affected by this issue
SQL Server Native Client 11.0 – Affected by this issue
ODBC Driver 11 for SQL Server – Not affected by this issue (I’ve got a report from someone claiming this driver is also affected, but it might be a mix-up with Native Client 11.)
ODBC Driver 13 for SQL Server – Not affected by this issue
ODBC Driver 17 for SQL Server – Not affected by this issue
ODBC Driver 18 for SQL Server – Not tested, but most likely also not affected by this issue

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.

Share this article:

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.