Microsoft Access: Issues with SQL database access because of ODBC driver?

[German]Brief information for the readership and at the same time the question if someone is already affected. I received a user message that Microsoft Access suddenly had problems with SQL database access. All databases suddenly showed the value '#DELETED' in the tables. The problem is the Microsoft SQL Server ODBC driver (shipped with Windows), which seems to go on strike after Office updates.


Advertising

ODBC access to SQL server is faulty

German blog  reader Axel H. contacted me by mail on Saturday, May 28, 2022 to report a specific observation in the Microsoft Access environment related to accesses to a Microsoft SQL server. Axel wrote (translated):

enclosed is a note from me. I was called today by a colleague whose Microsoft Access suddenly did not want to work properly with the SQL Server work properly anymore.

All ODBC tables included in Microsoft Access show only '#DELETED' as content.

After some searching, I found out that it was the 'original' Microsoft SQL Server ODBC driver, the one that comes with Windows.

Sounds bad, a manual fix is required.

New ODBC driver versions work

Microsoft ODBC Driver for SQL Server is a single dynamic-link library (DLL) that provides runtime support for applications that use native code APIs to connect to SQL Server. Microsoft ODBC Driver 18 for SQL Server should be used to create new applications or enhance existing applications that use the newer SQL Server features.

The current version 18.0.1.1 dated February 15, 2022 is offered for SQL Server 2022 Preview here, on Github there is this overview about different driver versions. After some testing, Axel was able to determine that newer versions of the ODBC driver such as v13 and v17 do not show the problem when accessing the databases.

Trouble on the way, Office rollback helps

Axel wrote, that the version, supplied by Windows, is used however surely to >90%, in the field – since Windows brings it along (see here for instance) and does not have to be installed extra. So there could be a lot of trouble within the next days. Axel told me, that Microsoft Office Professional Plus 2019 C2R 32-bit, version 2205 build 16.0.15225.20028 dated 2022-05-24, was affected in his environment (no idea, whether other Office versions are affected). After he did a rollback of Microsoft Office to version 16.0.15128.20248 from 2022-05-17, everything worked again immediately.


Advertising

Axel concludes that the problem is not really with the ODBC driver, but with the Office update and how it 'cooperates' with the ODBC driver. He has therefore deactivated the Office C2R updates via the registry, so that the latest version is not immediately applied. Anyone else with such experiences?

Addendum: There are others who observed this, see the comments to my German blog post.


Cookies helps to fund this blog: Cookie settings
Advertising


##1

This entry was posted in issue, Office and tagged , , , . Bookmark the permalink.

7 Responses to Microsoft Access: Issues with SQL database access because of ODBC driver?

  1. Ben says:

    OMG thank you so much. Just started to invoke DR which would have been useless because the same fault would have been showing. Will try newer drivers now and see if than helps. There will be a shit-storm in the next few days, I guarantee it! Nice one MS – of the 4 DR incidences we have suffered, ALL of them have been caused by Microsoft. Cant wait until we finally move on to Open Source and we can then enjoy a stable environment.

  2. john turczak says:

    I have multiple clients with this issue and have spent the last coupled of days updating their sql odbc driver to a new version and resetting up the system. a complete night mare.

  3. Daniel says:

    Thanks, this solved an issue for a customer. Oddly enough, the bug only affected one table. This table had a VARCHAR key, whereas the unaffected tables had INT keys. Perhaps the issue lies in handling of tables with VARCHAR primary keys?

  4. Advertising

  5. Stefan Rairigh says:

    We had two users with this exact issue today. Of course somewhat mission-critical Access databases that manage our tooling as well as employee time and attendance and they are still down. The The office version affected here is 365 MSO (Version 2205 Build 16.0.15225.20028) 32-bit.
    The version on my system is NOT affected: 365 MSO (16.0.14326.20936) 32-bit.
    None of the systems including mine find any available updates when checked.
    Our SQL Server 2017 version is 14.0.2037.2

    Can you clarify if there is something we can do prior to a public fix/update from Microsoft? I'm not sure how we do a rollback as we don't download specific versions.
    If we replace the ODBC driver, does that require a reconfig of the database?

    • guenni says:

      I don't have this issue – it was reported by blog readers. None of the admins who used a new ODBC driver reported that he have to reconfig of the database.

    • Stefan Rairigh says:

      Just a follow-up to this issue. For our users, the issue has resolved itself overnight. We were going to test/deploy the new ODBC driver but didn't end up needing to. We see no applied updates on either the SQL server or the user systems, but the issue is no longer present.

  6. Hanns says:

    We are observing similar problems with ODBC drivers on MYSQL/MariaDB databases. Update from MySQL ODBC 5.1 Driver to MySQL ODBC 8.0 ANSI Driver solved some of the problems. I share the same obersvation, that tables with VARCHAR Primarykeys still showing problems when handled in views. Problems we observer are: #deleted records; linked tables not editable when filtered in views.

Leave a Reply to john turczak Cancel reply

Your email address will not be published. Required fields are marked *