20266 Operation not supported

[mellamokb]

You probably need the 64-bit version of LINQPad since you have the 64-bit Access/drivers installed (look for the message Running massive queries in LINQPad 5? on the download page). I'm not sure if it will work with the script I provided with no other changes, as I've never used 64-bit Access and don't have a machine with it installed that I can experiment with.

 

[isladogs]

Thanks
I downloaded the 64-bit version of LINQPad with the following results

• Using CC version 2511 19426.20186 64-bit, the script failed on the line var rs = .... with the error Operation is not supported for this type of object
• Using Beta version 2601 19611.20002 64-bit .... same error
• Rolling back to CC 2509 19231.20216 64-bit . . .success

NOTE: I couldn't find build 19231.20246 - was that another mistake in your post?

Anyway, assuming I've now correctly understood the problem, I'm happy to inform the Access team about the issue tomorrow.
I'll let you know if I get a response.

 

[mellamokb]

Awesome glad to hear! Yes this is exactly the issue, appreciate your help in reviewing this. And thanks for your patience sorry for all the posts to get to the explanation. I really hope they plan to fix this but I could understand we are probably well outside of normal usage.

 

[isladogs]

One thing. When the query ran successfully in version 2509, the output in the LINQPad window just showed the number of records (1) but not the actual data. Is that normal?

I agree that this is very unusual usage so it may not be a high priority to fix. If not you will indeed need to find a workaround or freeze updates at version 2509

Anyway I'll email the A-team attaching the repro files and try to explain the issue succinctly

 

[mellamokb]

Yes that is just a simple test to demonstrate the issue, so I output the rs.RecordCount value. In the production code we read out all of the recordset fields and rows into objects for processing from many tables, and then insert thousands of rows back into tables at the end in a transaction.

The reason we adopted DAO for all C# processing is because it is orders of magnitude faster than OLEDB, and this is a high-scale performance sensitive usage. Especially when saving records, it can be up to 10x faster to create records with DAO than using INSERT statements over OLEDB. For retrieving records it is 2-3x faster than OLEDB. Also, with DAO we can use workspace transactions for easy rollback across databases, but it is very difficult to do this with OLEDB. It took some effort to build clean and reliable COM wrappers but we have a nearly 100% error-free C# DAO library and would like to keep using it that way for the performance/usability gains.

 

[isladogs]

If it works for you then that's great. I'll let you know if and when I get a response from the A-team

 

[tvanstiphout]

I am on A365-32 MEC v2510 and I also get the error with the heterogeneous query.
The only way I can make it work is to attach the table from test2.accdb. I realize that would be a massive change to the OP's app, and may not be practical.

 

[tvanstiphout]

We have another report with the same issue here.

 

[isladogs]

I have already had a response from the Access team.
They are aware of the issue which was triggered by a security fix released on Tues 9 Dec.

I will give a more detailed response and hopefully a workaround once I have had further confirmation from the team

 

[isladogs]

Further to my previous post:
The Access team are aware of the issue which was introduced with a security fix on Tues 9 Dec.
They are currently investigating for workarounds, and whether a change could be made to allow cases such as yours.

In the meantime, they have referenced this older article from 2021: KB5002984: Configuring Jet Red Database Engine and Access Connectivity Engine to block access to remote databases - Microsoft Support

This involves adding or editing a registry key, AllowQueryRemoteTables. In my case using 64-bit Access 365, the key location was:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines

For 32-bit Access 365 on 64-bit Windows, the key will be at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines

See the article for other variations

To use this as a workaround, you need to set the AllowQueryRemoteTables DWORD registry value to 1 so that these queries will be allowed to run. By default, if the key is missing, or set to 0, then queries that have external references in them are blocked unless they are run by Access using a trusted database.

I have tested this registry ‘fix’ using Beta Channel 2601 and the query ran successfully using your LINQPad script when the key value was set to 1. The COM Exception error occurred when it was set to 0

However, be aware that if you use this workaround, you are restoring the previous levels of access to that prior to 9 Dec and therefore increasing potential vulnerability. Your code will run but there is a chance of other malicious code also running.

The Access team also stated that there will be an updated article with more information to follow.

For info, there have also been several other reports of what may be the same issue:

These links were provided by Philipp Stiefel:
https://stackoverflow.com/questions...working-in-access-operation-not-supported-for
https://learn.microsoft.com/en-us/a...-pivot-tables-linked-to-access-not-refreshing
https://learn.microsoft.com/en-ca/a...e-update-has-stopped-ms-query-operation-not-s

This one is from Karl Donaubauer
https://learn.microsoft.com/en-us/a...ing-a-remote-table-in-access-sql-error-8000-4

And yet another from Tom van Stiphout (see post #28)
accesing a remote table in access sql error 8000-4005 - Microsoft Q&A

 

[isladogs]

Here is a further update on issues related to the security fix released on 9 Dec:

The fix changed the default setting which is now to block queries to external databases.
This is the case whether the query was done using a script, from Excel or from Access itself

Previously such queries were by default allowed if the registry key mentioned in post #30 was either missing or had been manually created by the user and set to the value 1. The value 0 disables such queries.
Now the default is to block such queries if the registry key does not exist. The registry key itself is not created automatically.

When the query is blocked (from Access or Excel), this error occurs

From Access, queries to external databases will only run if the host database is trusted (location or document) or the registry key exists and is set to 1. Interestingly, in my tests from Access, the external database does not need to be trusted (which I find incongruous)

The Access team is now working on a fix which will hopefully allow certain types of queries to external databases to run successfully.
This will be released first to the insider channels (beta/CCP) then progressively to slower channels (CC, MEP, SAP) in the coming weeks if no further issues arise.

However, it is possible that some scenarios will remain blocked unless the registry key is added and set to 1

EDIT:
1. Minor changes to wording above to clarify meaning
2. Karl Donaubauer has now written an article on this topic at Access Forever: Query Reference to Remote Database Fails

 

[sonic8]

Now the default is to block such queries whether or not the registry key exists.

This sentence is misleading. It should read: "Now the default is to block such queries if the registry key does not exist."

 

[isladogs]

@sonic8
Agreed. I've just edited that sentence as in your post and added a link to Karl's article for AFo

.