MSUEDW - MS Access Data Tables Showing #Deleted Instead of Actual Values from MSUEDW

This article details how to fix an issue where data pulled from EDW Database does not display properly in MS Access.

Problem

Since mid-January, 2022, when viewing Data Tables in Access connected to EDW, the columns no longer show the actual data, but rather ‘#Deleted’ values. Users can still run queries and get actual data, but new queries cannot be created.

Environment

MSUEDW

Resolution

1. Update ODBC Driver to version 12.1 or newer
   • 12.2 instructions courtesy of Hiran Jayasekara: Oracle 12.2 Client Installation Guide (link).
2. Once updated, Open ODBC Data Sources(Match the bit version to the bit version of Access used by the end user)
   1. Ensure the top two check boxes are checked:
      1. Bind TIMESTAMP as DATE
      2. Force SQL_WCHAR Support
      3. Pre-fetch size for LONG column data (This isn’t required but is a fix for any truncated errors when viewing tables)
   2. Test connection to EDW via ODBC driver configuration
3. Open the .accdb file in Access the user works with and go to File > Options > Current Database and check the box for: Support Date Time Extended (DateTime2) Data Type for Linked/Imported Tables
   1. You MUST have a db file open in Access for the ‘Current Database’ option to appear
4. Reconnect the tables in one of two ways:
   1. Make a new blank DB in Access and manually link the EDW Tables you need and save as a new working file
   2. Delete all of the linked tables in Access, then manually relink
5. You're done! This should now present the actual data in the data tables.

Cause

Unknown