SQL Anywhere 17 some tables show #deleted via ODBC

[InstructionWhich7142]

Hi,

With the latest SQL Anywhere 17 64-bit drivers direct from SAPs website and 64-bit MS Access freshly installed from Office365 website on Windows Server 2022 I get #Deleted instead of data for some tables - seemingly ones using an ID/PK field of the BigInt type - loading these as Snapshot instead of Dynaset works, is there any longer term fix for this? It seems very similar to the below issue with SQL Server that was fixed a couple of years ago?

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

As per this thread here https://answers.microsoft.com/en-us/msoffice/forum/all/ms-access-displays-deleted-in-every-field-for/593e5f12-3cbd-414e-841c-9df612504d3c?page=3 Originally about Oracle, we have now seen this in two Azure BE client databases in the last 2 days, making me think another...

www.access-programmers.co.uk

Thanks,

 

[tvanstiphout]

Did you follow the advice of #10 post in that previous thread?

 

[InstructionWhich7142]

Yep, it prompted me when I first tried to connect to either basically enable that or stay in old compatibility mode, I enabled it, I've just closed and re-opened the database and it's still ticked in "current database"

 

[sonic8]

I enabled it, I've just closed and re-opened the database and it's still ticked in "current database"

Relink the Sybase tables.
Configuration changes affecting ODBC linked tables usually only become active after the tables are relinked with the new configuration.

 

[InstructionWhich7142]

Relink the Sybase tables.
Configuration changes affecting ODBC linked tables usually only become active after the tables are relinked with the new configuration.

It's SQL Anywhere not Sybase, sorry.

I have tried closing and re-opening and adding new links etc. To be sure it wasn't some kind of "these were linked before" error I created a new blank database, manually enabled that setting, saved and closed then re-opened and created fresh links, still got #deleted

 

[tvanstiphout]

Can you create a new table, with only one field, bigint, and make it the PK, and see if linking to it causes #Deleted?
You may also want to temporarily turn on ODBC tracing.

 

[sonic8]

It's SQL Anywhere not Sybase, sorry.

It was Sybase SQL Anywhere for ~20 years before SAP bought them.

[...] still got #deleted

Review all options in the ODBC driver configuration dialog whether there is something related to BigInt.
There was a "Keys in SQL Statistics" option required for Access according to some documentation. - However, it doesn't seem applicable here, as your problem only affects tables with BigInt keys.

Creating and reviewing an ODBC trace file, as Tome mentioned, might also help to pin down the source of the problem.