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

[Lightfoot]

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.

Thank you for reminding me about Views, as I'm a little green.

 

[Lightfoot]

Can anyone affirm that the prior version of Access doesn't have this issue, version 2203? We are about to roll back but thought I'd ask the group...???

 

[isladogs]

The #DELETED bug affects 2205 only AFAIK

Bear in mind that if you add an index to your view to make it editable, it will again show #DELETED. Catch-22
Another solution is to convert to a local or linked Access table

I would do the simplest possible solution as a solution should be released soon

 

[greg28818]

Wow...this bug is a PITA. While I wait for MS to send out a fix, people have suggested the ODBC Driver 17 for SQL Server is unaffected to this problem. What is the difference between using the ODBC Driver 17 for SQL Server and SQL Server native Client 11.0 driver? I have always used the SQL Server Native Client driver for making connections to various SQL servers and don't know what the difference is. I can ask in a separate thread if need be. Thanks for any clarification on the difference.

 

[isladogs]

The old Native Client drivers are affected by this bug as is the even older default SQL Server bug from 2000.
For whatever reason, the newer ODBC drivers such as version 17 aren't affected.
In theory these are also more efficient and have better performance BUT the driver will need to be installed on each user's workstation.

 

[NicolasMous]

The old Native Client drivers are affected by this bug as is the even older default SQL Server bug from 2000.
For whatever reason, the newer ODBC drivers such as version 17 aren't affected.
In theory these are also more efficient and have better performance BUT the driver will need to be installed on each user's workstation.

My 2 cents as well.

We have about 150 users using our ACCESS application with linked tables to there SQL server.
We always used the Native driver 11.0 to establish the DAO (linking the tables) and ADO connection (direct queries to the SQL server). Since this issue arrived we were, sort of, obligated to use the newer driver(s). By doing this we also separated the DAO and ADO connection strings, where the DAO (ADODB) connection string uses the ODBC 17 SQL Driver and the ADO connection string uses the latest OLE Driver 19.

I also tested it with ODBC driver 18 but this does not seem to work (also getting the record is deleted error)...

By using the new drivers the error does not appear in the latest office build 2205, BUT one thing that is drawing my attention is that the DAO queries (requesting / opening forms with more records) is A LOT slower than the native 11 client... This seems to be even more so when the end user uses a SQL 2012 server.

Any of you know what is causing this? The strange thing is that at some of our users, which are using SQL server 2019, and overall have a better performing server, the new drivers are not particularly slower than the native 11 driver. Though I can't imagine that the different drivers can have this much speed-impact on the different servers.

 

[isladogs]

MS released the fix to the Insiders Channel yesterday and it should be available for the current channel probably later today

 

[Pat Hartman]

Just to recap, changing the driver in the DSN doesn't solve the problem. You would need to delete the links and link from scratch for this to work. So, Adding a new DSN for the new driver and relinking to that DSN is probably easiest.

Don't forget, you need to distribute the SQL Server 17 ODBC driver to every user computer.

 

[isladogs]

Actually, you can edit the driver in the connection string using new linked table manager and just refresh the links. No need to delete & re-create from scratch.

However, as already stated the fix has been promised for all in the Current Channel sometime today so it may be worth doing nothing and just waiting a few hours if you can.

 

[Pat Hartman]

Actually, you can edit the driver in the connection string using new linked table manager and just refresh the links. No need to delete & re-create from scratch.

Isn't that what the recent poster said did not work? Maybe he edited the DSN using the DSN tool and since the DSN didn't change, no refresh happened.

 

[isladogs]

That is the approach I used to test this bug in the first place switching between the various SQL Server drivers on my machine.
I've just retested it & the only extra things I had to do was enter the username / password combination and redefine the index on a view.
No need to close & reopen the database

I haven't tried using the ODBC wizard

 

[isladogs]

For info, MS have just announced that the #Deleted bug fix is now available to all in the Current Channel, Current Channel (Preview), Beta, and Semi-Annual Channel (Preview).
It does not require a new build but will require closing and restarting Access to see the change. In other words, the fix was done by rolling back the change that caused the problem

Just tested & now working for me

 

[Lightfoot]

Our people couldn't wait for the fix and went back to the prior Access version, 2203. Thank you for all the information.

 

[isladogs]

No problem ... though 2204 was also OK
Anyway, they should be able to revert to 2205 now if they are in any of the listed channels

 

[AnnatarsGift]

Isn't that what the recent poster said did not work? Maybe he edited the DSN using the DSN tool and since the DSN didn't change, no refresh happened.

Yes, using the RefreshLink method doesn't check for driver changes in the DSN. The DSN connection details once used must be cached somewhere against each table (in particular the driver used). It's similar to the way the username and password credentials are stored somewhere when connecting, yet they are hidden from view and not displayed for obvious reasons. Changing to another DSN certainly forces it to check the DSN details. I'm not sure if changing other details of the connect string would also force it to check the DSN.

You can explicitly specify the driver in the connect string. That is what isladogs was referring to being an option via the new "Linked Table Manager" screen. Alternatively you can close the Access app / database and reopen it, which then forces it to check the DSN details again when reconnecting. Certainly there's no need to remove and recreate the linked tables from scratch.