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

.

 

[mellamokb]

I saw there was a new release to the Current Channel on Thursday - 19530.20138 - I tested it with the script above and still get the same error. Do we have any updates on whether this is still being addressed with another release?

 

[sonic8]

Do we have any updates on whether this is still being addressed with another release?

As far as I'm aware, Microsoft has not yet announced a fix for this issue.

I suggest you monitor the corresponding post Query Reference to Remote Database Fails on AFo for updates on this issue.

 

[isladogs]

As previously mentioned, the Access team were working on a fix and were planning to release it in stages starting with Beta Channel then CCP before moving to CC. The last message I received stated that MVPs would be informed when the fix rolled out.

I have tested in the latest Beta channel release and am still getting the error both with your script and when running a query referencing an external database from an untrusted location

 

[isladogs]

Response from the Access team:
The fix will be in the next Beta and CCP releases so hopefully sometime in the next week or so.
I'll let you know when that happens and also test it myself

 

[isladogs]

@mellamokb
The fix was released to the Beta channel in version 2602 build 19711.20000 on 13 Jan. It will shortly roll out to CCP and then CC.
Unfortunately, the fix is very limited in terms of which scenarios have been included and it will NOT help you with your script based process.
After initial testing, the scope of the fix is much more restricted than I had originally expected.

It is important to remember that MS consider this to be a security fix and NOT a bug
As a result, they were never going to reverse it to allow all the actions they wanted to block

You will therefore need to do one of the following work-arounds:
EITHER
a) create or edit the AllowQueryRemoteTables registry key and set the value = 1 (see post #30 for details). Doing this will revert Access security back to what it was before the 9 Dec security update and allow you to keep using your script
OR
b) change your approach to use linked tables either in Access or from Excel

Sorry to disappoint you after all this time.

The Access team member responsible has given permission for me to quote his explanation here. It directly relates to your specific issue and the repro db that I forwarded:

The change is NOT targeted at the scenario here where this query is run from test1.accdb
var rs = db.OpenRecordset(@"SELECT * FROM tblItem1 INNER JOIN [G:\MyFiles\UserDatabases\mellamokb_AWF\test2.accdb].tblItem2 ON tblItem1.ID = tblItem2.ID");

In this case, you are making an external reference in the query in test1.accdb to a table in test2.accdb in the SQL query.
This is the behavior that the original security fix is designed to block.

The update addresses the common case of creating a connection, for example in Excel, where the data source is specified as G:\MyFiles\UserDatabases\mellamokb_AWF\test1.accdb\test1.accdb, and then the SQL query used looks like: Select * from [G:\MyFiles\UserDatabases\mellamokb_AWF\test1.accdb].tblItem1.

In this scenario (which Excel creates by default), there is no need to block the query, since although it has an external reference literally, the reference is to the same source specified for the connection, but it was being blocked anyway.

For your case where you really do want a join between multiple sources, you either have to set the registry key to get back the old behavior, OR create a link to [G:\MyFiles\UserDatabases\mellamokb_AWF\test2.accdb].tblItem2 in test1.accdb, and then write the query as "SELECT * FROM tbleItem1 INNER JOIN tblItem2_lnk ON tblItem1.ID = tlbItem2_lnk.ID"

I apologize if I didn't make that clear.

You may also want to read Karl Donaubauer's update to his article on this issue at AccessForever:

Query Reference to Remote Database Fails

Currently, there are errors with queries/SQL that refer to remote databases. Is it the intended security feature or a bug?

www.accessforever.org

 

[mellamokb]

Thanks for the update. We also found in the meantime that another of our products was affected in a similar way that wasn't even on my radar but does a similar IN clause query from a .NET application.

In our use case the only viable workaround will likely be to enable AllowQueryRemoteTables registry key. That's a bit unfortunate since it will have wider impact than just our application on the customer servers where it is deployed.

I would have preferred the dynamic IN clause do the same registry checking for Trusted documents, seems like it would be trivial to program and we are more than happy to ensure our applications have the right Trusted registry keys set.