New Access / SQL backend bug - #Deleted showing in Tables

[Minty]

As per this thread here

Redirecting

Originally about Oracle, we have now seen this in two Azure BE client databases in the last 2 days, making me think another silent update has been applied.

It appears that some versions of Access can no longer see tables that have an nvarchar() primary key specified.

The fix appears to be to change the field to a varchar data type, or add a new primary key numeric ID, and set the original PK field to be unique and not allow nulls.
This should retain your functionality without a ton of reworking.

You have been warned.

My colleague can't see the same problem using 2204

I can using 2205 and I get the error

 

[isladogs]

Just tested and can confirm the new Maybug described by Minty
However, according to Philipp Stiefel, changing the driver to ODBC driver 17 for SQL Server fixes the issue - I've not yet tested this

 

[Minty]

I haven't yet experienced this.
However, according to Philipp Stiefel, changing the driver to ODBC driver 17 for SQL Server fixes the issue

That's interesting, in both cases these were legacy databases that were converted to SQL backends some time ago and as a result, using the Version 11 Driver.

We use version 17 across the board in any more recent developments, and to be honest, almost 100% of the time use an integer Identity PK field.

 

[sonic8]

As @isladogs mentioned, I can reproduce the issue.
I've written a summary of the problem here: Bug in Access V2205 - SQL Server NVarchar Primary Keys cause #DELETED in linked ODBC tables

 

[isladogs]

@sonic8
I'm guessing/hoping this could be a bug that is easily reversible using the MS remote switching technique

 

[cheekybuddha]

Yay, go Access!

 

[GPGeorge]

As @isladogs mentioned, I can reproduce the issue.
I've written a summary of the problem here: Bug in Access V2205 - SQL Server NVarchar Primary Keys cause #DELETED in linked ODBC tables

I wonder if that would apply to ODBC driver 18 as well.

 

[isladogs]

Just for information, if you convert a linked SQL table affected by this bug to a local table, the data is 'magically' recovered.

Thar reminds me of another bug dating from 2007 & now 15 years old:

A Complex Deep Hidden Attachment Mystery

This is the fifth in a series of security challenges. In this case, a problem occurs when deep hiding attachment fields and a solution is provided. But why does the solution work?

www.isladogs.co.uk

By contrast, I suspect the Maybug will be fixed VERY quickly with a remote switch / rollback

 

[Pat Hartman]

There are similar problems when tables have BigInt PK's. I got around that one by creating a view. That worked for me because I didn't have to update the table. It was data that was downloaded from our transaction processing company and was used for analysis only.

Does anyone know why Office installs only the ancient SQL Server ODBC driver and not the latest one available? It is really a PITA to have to also distribute ODBC drivers when you want to add a new user to your app. I think the Excel people must run into these problems also.

 

[isladogs]

There are similar problems when tables have BigInt PK's. I got around that one by creating a view. That worked for me because I didn't have to update the table. It was data that was downloaded from our transaction processing company and was used for analysis only.

It had never occurred to me to set a PK value as bigint until now
But a quick test confirmed that's only an issue if you haven't ticked 'Support Large Number (bigint) ....' in Access Options

 

[sonic8]

I wonder if that would apply to ODBC driver 18 as well.

I guess the ODBC Driver 18 is also not affected by the problem, but I haven't got it installed and haven't had any time yet to do more research on which drivers are affected and which are not.

Just for information, if you convert a linked SQL table affected by this bug to a local table, the data is 'magically' recovered.

Sure. There is nothing wrong with the data at all, the problem is just the ACE/JET dynaset mechanism to fetch data by PK.
If you create a select query in Access querying the "#Deleted" table and configure the query to create a Snapshot recordset, the data will also be displayed correctly.

The #Deleted Maybug appears to have now been fixed

I can't confirm that yet. I still see the issue, even after relinking the table in question.

 

[isladogs]

Just deleted my post as you answered. I'm re-checking. The Maybug issue was fixed in one db but not another

 

[GPGeorge]

"Does anyone know why Office installs only the ancient SQL Server ODBC driver and not the latest one available?"

No, but I have a good hypothesis involving the lack of incentive to overcome decades of inertia.

 

[Pat Hartman]

But a quick test confirmed that's only an issue if you haven't ticked 'Support Large Number (bigint) ....' in Access Options

I ran into the problem before that option became available. Also, once you set the option, you can no longer open the app in older versions of Access so I don't use it unless It it necessary.

 

[ppaton]

Just ran across this in build 2205 connected to SQL Server, but oddly only in one table out of about 75. I also noticed the data reappeared if converted to a local table. I duplicated the table in SQL server, added an identity field, made it the primary key and linked the empty table to Access, then appended all the data from the local table into it. The newly created table link worked fine, which I am attriubing to the int PK in the new table. The PK in the original table is a varchar(50). So this problem isn't limited to ints or bigints. It's varchar also.

Re linking the table with ODBC Driver 13 for SQL Server corrected the problem.

Stress levels stabilizing...heart rate is returning to normal now.

Thanks to all for figuring all this out.

 

[isladogs]

I've read reports suggesting its not just SQL Server tables with nvarchar PK fields that are affected.
Possibly also tables from other ODBC sources.
There may be a wider issue with how Access is interpreting certain types of linked ODBC table data

Another workround that doesn't require altering the original table is to create a read only view based on the table
This is, of course, equivalent to making a snapshot query in Access based on the table

For now, the bug hasn't yet been publicly acknowledged by MS nor has it been fixed (despite my earlier misplaced comment)

 

[AnnatarsGift]

I thought I should stop lurking and actually contribute. Thanks for that summary (sonic8) which was the first confirmation I could find that helped me isolate the issue and resolve it by rolling back to build 2204.

Just to muddy the waters a little, I have two points to add.

1) This issue hit our systems fully this week with build 2205. But a full month before (which makes no sense as it was pre build 2205) we had a couple of isolated instances of the issue on user's systems. At that time I assumed it was a recurrence of one of the previous build issues and so immediately checked the versions of Windows, ODBC and Office to see if they were up to date. But they all matched my dev and test environments where the issue wasn't occurring. More annoyingly the issue magically disappeared the day after in each case. I wish I had taken note of the build data to be 110% certain.

2) This week it hit my dev environment and after finding sonic8's summary of the issue I immediately went to town on switching the ODBC drivers used. Thanks to similar ODBC issues with recent builds it was a simple switchover process, yet the problem was not resolved by using the "ODBC Driver 17 for SQL Server". However, I was getting inconsistent results. For a period of 5 minutes the issue was resolved and working with the original ODBC driver. I couldn't figure out what happened for those 5 minutes before it went back to consistently not working with any ODBC driver. The day after while I was still trying to figure out what was going on, it then hit the live systems and a rapid rollback to build 2204 was the only solution. Perhaps there was some residual connection somehow being maintained by Access as I was not restarting the app between switching the DSN driver settings and refreshing ODBC links.

As I'm writing this I'm back on a test system with build 2205 and it's working fine with either ODBC driver. This does not make any sense! I shall return when I figure out what the hell is going on...

Okay! Inconsistencies are now explained. Ignore point 2 above. Changing the DSN to use another driver and refreshing the links in Access does not correctly pick up on the change of driver. It's like Access ignores the refresh, I assume when there's no change to the connect string stored against the linked table. I should have thought of that sooner. With two differently named DSNs and switching between them I get consistent results, one driver works and the other doesn't. Sorry for any confusion.

I could have avoided rolling back the build and just switched the ODBC driver. Doh!

 

[Lightfoot]

Just joined, grateful for all I've read, struggling to solve this for group of production users. MY FINDINGS: Issue arose May 31, tied to several SQL Server tables (v 15.0.4223.1), with Access Office 365 2205 (v 15225.20204) via RDP connection for all users (nice, so settings are centralized to one PC). We just updated to ODBC (v 2017.179.01.01) last night with no benefit. All SQL primary keys are defined as varchar (between 10-255). I can fix some #DELETED results for linked tables simply using "Refresh Link", but not others, and that refresh held overnight. Also changing from dynaset to snaphot makes forms and queries work for the other bad tables (but users still must somehow edit). As of this minute, I observe that tables tied to SQL Views are working, but not those tied to SQL Tables... so researching that now.

An hour later... In addition to the Refresh Links (above) I learned that using SQL Views as sources instead of SQL Tables seems to be a workaround. Can anyone else confirm this?

 

[AnnatarsGift]

Just joined, grateful for all I've read, struggling to solve this for group of production users. MY FINDINGS: Issue arose May 31, tied to several SQL Server tables (v 15.0.4223.1), with Access Office 365 2205 (v 15225.20204) via RDP connection for all users (nice, so settings are centralized to one PC). We just updated to ODBC (v 2017.179.01.01) last night with no benefit. All SQL primary keys are defined as varchar (between 10-255). I can fix some #DELETED results for linked tables simply using "Refresh Link", but not others, and that refresh held overnight. Also changing from dynaset to snaphot makes forms and queries work for the other bad tables (but users still must somehow edit). As of this minute, I observe that tables tied to SQL Views are working, but not those tied to SQL Tables... so researching that now.

Views will be equivalent to snapshots with the data not being editable. That's why they will not be effected. Good luck fixing the rest.

 

[isladogs]

MS have now acknowledged the bug and promised it will be fixed soon - see post #8 in this thread at UtterAccess.com