Microsoft Access users may start experiencing #Deleted in all columns when adding new records to forms bound to SQL Server tables containing identity columns as of 64-bit Office 365 Version 1911 Build 12228.20364.
The issue occurs when linking SQL Server tables to a Microsoft Access database using the 64-bit version of Office 365 Version 1912 build 12325.20288 released on January 8th, 2020.
UPDATE: Office 365 Version 1912 build 12325.20298 released on January 14th, 2020 is ALSO affected by this issue.
UPDATE: 2020-01-17: Microsoft confirms the issue publicly: https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Access-54962069-14f4-4474-823a-ff7e5974a570
UPDATE: 2020-01-23: Microsoft Released Version 1912.12325.20344 that fixes the issue covered in this blog post. https://docs.microsoft.com/en-us/officeupdates/monthly-channel-2020#version-1912-january-22 From that URL “This update fixes an issue that can cause Microsoft Access to fail to identify an Identity Column in a linked SQL Server table, which can cause rows to be reported as deleted incorrectly”
The issue is as follows:
In SQL Server, assume you have a SQL Table with a column where the Identity property is true like this:
When linked to Microsoft Access via an ODBC Link, that ID column normally appears in Microsoft Access as Data Type “AutoNumber”:
When linked from Build 12325.20288, you can see that the same field now links as “Number” (Field Size: Long Integer), but it no longer recognizes the identity property from SQL Server:
on Office 365 Build 12325.20288 64-bit
The effect of the Data Type being set incorrectly on the linked table is that an end-user after entering a new record in the Access Form bound to that table will see #Deleted in every field/column of the new record when they leave the record like this:
If the user forces a refresh of the data by using the SHIFT+F9 keyboard shortcut, the refreshed screen will display the new record since the record actually was committed to the database. It is worth noting that end users who do not realize this may panic since the #Deleted in every cell may appear to them as data loss.
Work Around:
Two different workarounds appear to work.
Work Around #1 – Change the bitness level back to 32-BIt by using the MigrateArch setting in the ODT:
If you have a business reason why you must run Version 1912 Build 12325.20288, switching from architecture from 64-bit back to 32-bit does seem to work as a workaround. However if you do not need to run that build a much better solution is simply to revert to the stable Dec 10, 2019 Version 1911 Build 12228.20364. To switch the architecture, download a current version of the ODT Setup, then use the following XML:
|
Work Around #2 – Force Installation of Previous Build or the Fixed Version Build by specifying Version Explicitly in the ODT:
A better solution is simply to change to a version that does not contain the issue.
To force the installation of a particular build, first, use Control Panel Programs and Features to uninstall the existing Office 365 ProPlus build, then using the most recent ODT Installer download and then install the specific build using a config.xml file with the following contents:
OPTION 1: Revert to the last known good release of Office 365, which in my testing is Version 1911 Build 12228.20364.
<Configuration> |
OPTION 2: Skip to the fix Version 1912 12325.20344
The complete list of versions /builds can be found here: https://docs.microsoft.com/en-us/officeupdates/update-history-office365-proplus-by-date?redirectSourcePath=%252fen-us%252farticle%252fae942449-1fca-4484-898b-a933ea23def7).
The sample below jumps you to Build 12325.20344 – the first build where this issue is fixed:<Configuration> |
IMPORTANT: After you have changed the version of the ODT, you must RE-ATTACH the Linked Tables. Those tables previously re-attached by the corrupted application are still linked as Number, not “AutoNumber” so you must RE-ATTACH the tables for the identity columns to be re-linked as AutoNumber once again by the database engine.
The following instructions are intended for customers of Blue Link who encounter this issue with Blue Link Elite:
- Preparation Before Starting:
- First Delete everything from C:\ODT
- Download the LATEST ODT Installer from HERE: https://www.microsoft.com/en-us/download/details.aspx?id=49117
- Extract it to C:\ODT
- Create a new Install20191210Build.xml file with these contents in C:\ODT:
<Configuration> |
- Run setup.exe /download Install20191210Build.xml from Administrative Command Prompt
- Un-Install Microsoft Office 365 ProPlus using CONTROL PANEL “Uninstall a Program” option:
When uninstall is complete, install with the new ODT XML:
- From Administrative Command Prompt, run:
C:\ODT\Setup.exe /configure Install20191210Build.xml
When install is complete:
- Open Access as Administrator (this is so that the Blue Link Add-In can be registered correctly with the updated build of Access)
- Do not open a database, instead, hit the escape key on your keyboard and then click File > Account and verify that the build installed is the correct build:
- Finally, re-link the app to the SQL Database by re-selecting the data file from the Choose Company List:
- Open "D:\BlueLink\Master\BLElite64.accde"
- Click Choose Company
- Select the company in question and click Use
To test: Open a sales order, add a new line, leave the row. If no #Deleted, then the issue is solved.
Please comment and/or share this article with anyone else performing patch maintenance or managing a WSUS server / Office Updates.
Darren Myher's Blog 