20266 Operation not supported (1 Viewer)

[The_Doc_Man]

It is not at all unheard-of for an Office or Windows update to reset a registry key. Given the key you name, I'd bet on an Office update, but I don't use the feature implied by that particular key. And if it was something that worked on Tuesday and failed on Wednesday, ... you should be aware that MSFT pushes updates every other Tuesday night. When I was still a working man, my office used to dread alternate Wednesdays because of the chaos from Windows and other updates. You MIGHT go to the Start >> Settings >> Windows Updates panel and look for the Update History to see if you had an Office update Tuesday night or early Wednesday morning.

If you only had one such update (sadly, unlikely... but possible), you might be able to roll it back. Otherwise, you probably would have to go back and tweak that setting again.

 

[isladogs]

@mellamokb

Just to clarify...
Are you saying that the AllowQueryRemoteTables registry key keeps being removed?
Or do you mean that its value keeps being reset from 1 to 0?

Also, whichever of those you meant, does this follow a Windows Update? Or was there an Office Update?

 

[mellamokb]

Sorry I did not see your reply @isladogs - Yes, the registry key keeps getting forcibly removed. I haven't been able to correlate it obviously with a Windows/Office Update because I haven't kept close enough track. I am working on a .NET code project that heavily relies on IN clause feature for database joins between databases, and I'll notice that randomly on a morning when I go to start testing I am getting the Operation Not Supported error when it was working fine the day before. I would say it has happened 3-4 times this month since first using the Registry Key solution but I apologize I did not track the exact dates. It is wreaking havoc a bit on one of our processes for monthly billing which relies on a specific IN clause query to copy data between two databases in a deployed .NET app on our customer machines, and the fact that after working to get a solid batch script rolled out to our clients to install the registry key that it can be randomly removed is yet another wrench in this debacle.

 

[isladogs]

@mellamokb
Hmm. If I recall correctly, you are on Monthly Enterprise Channel so wouldn't be getting multiple Office updates each month.
That suggests that Windows updates are removing this setting but I'd still only expect that to happen on Patch Tuesday.

Next time it happens, can you please try to determine whether a Windows or Office update was responsible & try to pin down exactly which update was the issue. Thanks

I've just added that registry key again and set it to 1 for now so I can check up on it here also.

 

[isladogs]

@mellamokb
After further discussion with a member of the Access team, it transpires that the registry key IS being removed by Office updates.
Not by accident but as a policy.
I have just confirmed this on my own PC by doing an full (online) repair of the current beta build.

I have been given permission to pass on their comments:

Regarding the registry key being removed on updates: the key was designed as a temporary measure to give people time to adjust their applications, not as a long-term configuration option. We'd encourage anyone who is currently relying on it to look at restructuring their approach so they don't need it. For example, placing their databases in a trusted location, or rearchitecting queries that reference external tables. We understand that isn't always straightforward, but it's the more sustainable path.

Whether you (or I) like it or not, we have to remember the change was made for security related reasons and isn't likely to be reversed especially for what is definitely an unusual situation such as your own.

I have asked that the documentation be updated to make this completely clear.

So I think you have to choose between 3 possible courses of action:
a) disable all Office updates to prevent the key being removed in future
b) restore the registry key after each and every Office update
c) change your approach so you are no longer affected by this change

I'm sorry to be the bearer of bad news.

 

[mellamokb]

I am fine with the change and understand the security implications. What I'm not fine with, is not considering some use cases and just throwing customers under the bus (who they actually serve, not the other way around). We have been a diligent customer of Microsoft products and sold an advanced payroll platform on Microsoft Access for 30 years. Across our customer base they process payrolls for around ~40,000 companies across the US every week. I am deeply impressed with Microsoft Access as a database engine and continue to marvel at what it has been capable of. We are transitioning to and heavily reliant on another Microsoft product - C#.NET integration with Access - but treating Access more as a database than a full service GUI. This is a stepping stone on a path to leveraging yet more Microsoft products (SQL Server + Azure) for an eventual cloud lift.

I am sure we can work around with approach (c) above with some heavy effort on our end and a loss of performance and productivity in our products. Understand that we work with 10s or even 100s of GB of data in Access on our largest multi-tenant customers and so due to the 2GB limit we regularly split data around in many different files, and the IN clause is a very powerful way to leverage the Access Driver to perform JOINs for highest efficiency. But when integrated fully external (with C#.NET) it is totally impractical to preplan every possible way two databases might be joined, and make sure a linked table is loaded into each database ahead of time.

Perhaps we are the only company in the world using Access in this way, fine, so be it. But is it so much to ask, that someone, someone on the dev team consider a very, simple change. If an external program is talking to two access databases by joining tables together with an IN clause, and BOTH DATABASES are in a trusted Access location (which is what it has always been in our use case), the query should be allowed to run. It seems like a single line of the code to detect the same way that the UI detects when linking tables. I am very patient and will work through things but I am not a fan of being single-handedly dismissed as though our perspective is irrelevant.

 

[isladogs]

I can fully understand your POV.
I do feel that the changes could have been handled far better though the decisions made go beyond the Access team alone.
I have forwarded your reply in its entirety to the Access team

 

[tvanstiphout]

@mellamokb: I can understand that you are upset right now and I would be too. This may not be the best timing to say "you are abusing Access in the presence of better alternatives", but I'm saying it anyway, with all due respect.
Do I understand correctly: we have a commercial payroll app with Access FE and BE that grew larger and larger, and at some point it outgrew the max size of an Access BE so we decided to use a second one and use the IN clause to run multi-database queries, and 2 grew to 3, and worst case a few hundred.

Confronted with this scenario I would say that an Access BE is not secure and not a good place to store critical data, and also not as stable and incorruptible as server-class RDBMSes such as SQL Server. It (in its Standard edition) has essentially no limit to database size so all data can be together. Such database also has opportunities for server-side processing which in some cases can yield dramatically higher levels of performance. Even before I'm thinking about a cloud lift, I would be thinking about SQL Server on-prem with a single database.

A cheap hack until that time: perhaps you already have this, or can create it: a "Launcher" application that is used to start the payroll app. It is a small C# app that starts up, sets the registry entry, and then starts the msaccess.exe process.

 

[mellamokb]

@isladogs - Thank you for forwarding the message. I deeply appreciate your time and effort in helping understand and express this issue. We are in this situation of our own making because of our reliance on Access BE for such a long time, but it has been such a remarkably good solution for our use case all this time. I have to at least try to advocate on behalf of our team and product where I can. I don't mean to be hostile - I do appreciate the MS Access team trying to keep the product alive and updated as it matters greatly to us.

@tvanstiphout - That is a fair criticism - we are where we are I cannot deny We've always had a multi-database BE because it is a multi-tenant system with each tenant in its own db and a few core shared system db's but the 2GB has caused us to need to shard some of the system db's as they've grown. I am not upset as much just disappointed in the pace of change. In general for 30 years our solution has worked nearly flawlessly and most of our customers are in a multi-user environment running high-volume processing through our system with very few issues of corruption. Every solution has its pros and cons - Access BE is way more stable than people give it credit for, as it is remarkably well engineered, and it is very cheap to operate which is a tremendous advantage over almost every alternative. We are certainly working toward alternatives but we are a small team with a massive legacy product to convert..

I appreciate the thought on finding a solution. Our first iteration I spent a couple days researching and developing a robust batch script to install the registry key compatible with all versions of access. Then we bundled the script with our installer - this was supposed to be the solution until I discovered by accident that the registry key can get unset (and now we know that it's on purpose). We do run our product with a launcher already but setting a registry key requires admin access and most of our customers have teams that operate as low-level users without administrative access. But based on the response above I could see the registry key solution being removed altogether without forewarning.

We can find solutions, the perspective I'm trying to offer here is making consequential changes in the name of security and giving us two weeks to "deal with it" rather than engaging in productive dialogue about a solution that meets our needs while maintaining the enhanced security. This is not the first production issue we've had from a change rolled out through Office365. We've already encouraged our customers off Current Channel and some off of Monthly Channel, and at this point our most sensitive customers are on Microsoft Access 2010 runtime because production stability outweighs everything else. I would much rather be able to keep our customers secure and on latest product versions while keeping their business needs met and daily workflow stable.

 

[isladogs]

@mellamokb
The Access team member has responded:

Thanks for passing that along, and I appreciate the detail from the OP. It's clear they've built something substantial on Access and care about the platform.

However, I was also 'gently chided' by the Access team member for omitting an important sentence from his response that I provided in post #45

We are aware that some customers find this disruptive, and we're monitoring the situation. I can't promise any specific changes here, but we are listening.

In other words, they are monitoring the impact of this situation and looking at various suggestions from both customers such as yourself and from MVPs. All feedback is being listened to and being considered.

Another member of the Office team has made another suggestion for a possible workaround which might work for you. I'm currently requesting further details before I explain that suggestion

In the meantime, @tvanstiphout made a number of important points including Access not being a sufficiently secure solution for payroll data.

His suggestion (at least in the short term)) of using a launcher app to reinstate the registry key before running the app is worth looking into.
You could use Access for the launcher app but, as the registry key is in the HKLM hive, you would have to run Access as an administrator for this purpose.

 

[tvanstiphout]

Re HKLM: that is true, but it is POSSIBLE that the same key in HKCU will work as well. Test that first.
It may also be possible to run the Launcher as Administrator, yet launch msaccess.exe as standard.

 

[RonPaii]

Could you create a reg file with the key and merge it into your registry? Merge works on trusted locations without admin rights, or is that key in a restricted location in the registry?

 

[isladogs]

@mellamokb

OK I now have two potential solutions for you:

1. Registry hack
First of all, I tried adding the key to HKCU as Tom suggested - but it didn't work for me
To my surprise I was able to add the key to HKLM by a 'non-standard' approach

Attached is a .reg file (zipped). Unzip and save to a suitable location.
Now run this function from Access e.g. in an autoexec macro

Function AllowQueryRemoteTablesRegFix()
   FollowHyperlink "C:\FullPathToYourRegFile\AllowQueryRemoteTables.reg"
End Function

In my tests, it doesn't matter whether the file location is trusted. Nor does Access need to be run as an administrator.
It might only work for users who have Windows admin rights?
To my mind that it works at all is a potential security risk but that's a separate issue for another day!

2. Group policy
This was suggested by another member of the wider Office team (but I haven't tested it).
Create a group policy to ensure the registry key remains set with value = 1.
For example, create a PowerShell script to check and where necessary reset the reg key.
Use TaskScheduler to run the script on a regular basis.

Whilst the key wouldn't survive an Office update, it would be replaced the next time the script was run.
This could be setup by anyone with admin rights. Individual users wouldn't need admin rights.