New Access / SQL backend bug - #Deleted showing in Tables (1 Viewer)

Minty

AWF VIP
Local time
Today, 04:38
Joined
Jul 26, 2013
Messages
9,372
As per this thread here

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
1653564019885.png


1653564449041.png
 

isladogs

CID VIP
Local time
Today, 04:38
Joined
Jan 14, 2017
Messages
16,422
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
 
Last edited:

Minty

AWF VIP
Local time
Today, 04:38
Joined
Jul 26, 2013
Messages
9,372
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.
 

isladogs

CID VIP
Local time
Today, 04:38
Joined
Jan 14, 2017
Messages
16,422
@sonic8
I'm guessing/hoping this could be a bug that is easily reversible using the MS remote switching technique
 

isladogs

CID VIP
Local time
Today, 04:38
Joined
Jan 14, 2017
Messages
16,422
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:

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

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:38
Joined
Feb 19, 2002
Messages
36,908
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

CID VIP
Local time
Today, 04:38
Joined
Jan 14, 2017
Messages
16,422
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

1653649755237.png
 

sonic8

AWF VIP
Local time
Today, 05:38
Joined
Oct 27, 2015
Messages
613
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

CID VIP
Local time
Today, 04:38
Joined
Jan 14, 2017
Messages
16,422
Just deleted my post as you answered. I'm re-checking. The Maybug issue was fixed in one db but not another
 

GPGeorge

Grover Park George
Local time
Yesterday, 20:38
Joined
Nov 25, 2004
Messages
783
"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

Super Moderator
Staff member
Local time
Yesterday, 23:38
Joined
Feb 19, 2002
Messages
36,908
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

New member
Local time
Yesterday, 20:38
Joined
May 31, 2022
Messages
1
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

CID VIP
Local time
Today, 04:38
Joined
Jan 14, 2017
Messages
16,422
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

New member
Local time
Today, 04:38
Joined
Jun 1, 2022
Messages
3
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

New member
Local time
Yesterday, 20:38
Joined
Jun 1, 2022
Messages
4
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?
 
Last edited:

AnnatarsGift

New member
Local time
Today, 04:38
Joined
Jun 1, 2022
Messages
3
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.
 

Users who are viewing this thread

Top Bottom