20266 Operation not supported

[mellamokb]

Wasn't sure the best place to ask about this. But we noticed queries of this form:

Select *
From Table1
Inner Join [C:\Data\File.Accdb].Table2 ...

Are now giving an error 'Operation is not supported for this type of object' when running from DAO Database.Open call. It works fine in the Access query UI and worked fine in the previous version. Wondered if anyone else experienced something like this or where is the best place to report the issue?

 

[tvanstiphout]

What is the current version and bitness of Access you are on?
What is the previous version where it worked fine?

 

[mellamokb]

32bit.. I don't know exactly what version I was on before but I'm on the Monthly Channel. On another machine I have 2404 Build 17531.20190 and don't get the error.

 

[tvanstiphout]

It does not happen on my Monthly Enterprise Version v2510. I'm asking another tester to try it on his more aggressive versions.

 

[isladogs]

Following Tom's prompt, I tested this on Beta Channel v2601 build 19608.20000 64-bit. No errors occurred

 

[tvanstiphout]

At this point I am not convinced that there is a bug. The OP should simplify the problem, and on the failing machine create two new databases with simple names (without any spaces or other funny characters) and put them in a Trusted Location that also is a simple path, and create 2 simple tables, and try again.

 

[mellamokb]

So, I guess I didn't realize an important detail that mattered. It is specifically the use of DAO via COM interop with a C# script. Apparently this is getting a different result than using DAO via VBA code, which I am surprised to see a difference because I assumed them to be fundamentally identical since both are using COM interop against the same library.

So for example I have two databases, E:\test1.accdb with table tblItem1, and E:\test2.accdb with table tblItem2. Then I run the following C# script (using LINQPad for example, statically linked to Microsoft.Office.Interop.Access.Dao.dll):

var eng = new DBEngine();
var db = eng.OpenDatabase(@"E:\test1.accdb");
var rs = db.OpenRecordset(@"SELECT * FROM tblItem1 INNER JOIN [E:\test2.accdb].tblItem2 ON tblItem1.ID = tblItem2.ID");
rs.Close();
db.Close();
Marshal.ReleaseComObject(rs);
Marshal.ReleaseComObject(db);
Marshal.ReleaseComObject(eng);
rs = null;
db = null;
eng = null;

I also tested the following query and get the same result:

SELECT * FROM tblItem1 INNER JOIN (Select * FROM tblItem2 IN 'E:\test2.accdb') tblItem2 ON tblItem1=tblItem2.ID

We have been using this style of code for almost a year without a single issue before the install of the 20266 release build. Now when I run these queries I get this error on the OpenRecordset call:

COMException - Operation is not supported for this type of object.

 

[tvanstiphout]

Is E:\ a trusted location as I requested?

 

[mellamokb]

Just want to say - thank you both for engaging with this question, as I didn't really know where to turn for advice!

Yes, E:\ is a Dev Drive and marked as a Trusted Location with the registry key, makes no difference with the above. Also, a simple query against the database works fine like "Select * From tblItem1" so it is specifically to do with cross-database joins.

I tried another experiment where in the test1.accdb I added a query called tblItem2 which was coded as this, which is an older style we used for cross-database table queries.

SELECT * FROM tblItem2 IN 'E:\test2.accdb';

If I now run the COM DAO query from the script above as a simple "SELECT * FROM tblItem1 INNER JOIN tblItem2 ON tblItem1.ID=tblItem2.ID" I still get the "Operation is not supported for this type of object."

 

[tvanstiphout]

I speculate that this is a security issue that was "fixed".
You may have to fall back to what most people would do: attach the linked table and create the query that way. No IN clause. Surely that still works?

 

[mellamokb]

It does look like a linked table works with the code above. However that is untenable for our current design since we need to relink hundreds of tables every time the user switches clients, and the IN clause based method is by far the fastest. Our largest ISV customers have 10K or more client databases and switch between clients 1000s of time per day. I really hope this is a 'glitch' that will be fixed in a future version and not an intentional change. (Yes you can find many flaws with this design but it is in widespread production usage so we can't easily pivot).

I think I will try the Current Channel latest release and see if the problem is resolved.

 

[mellamokb]

So I have confirmation from another Dev on the same team that he was getting the same error for about a week, and then the error went away. As far as he can tell, no changes were made to his environment or configuration during that time. The error simply appeared, and then seemed to disappear of its own accord. We are both on identical releases of Office365 (20266). He doesn't even have the databases on Trusted Location path and the test script works perfectly.

 

[tvanstiphout]

I think you're now in the "collect more information" phase. Apparently, it works sometimes on some machines. What do they have in common?
Soon this may be seen at customer sites too, so it is an urgent issue.
Note that MSFT can turn features on and off remotely, so "no changes were made" does not always fly.

 

[isladogs]

I'm confused by your description of 20266 being the latest current channel release for 365.
This is the latest CC version / build

Can you show a similar screenshot on your system.

 

[mellamokb]

Sorry, I may have misspoke - we are currently on Monthly Channel on our entire team, not Current Channel.

Based on the release notes this appears to be the latest version and was released Dec 09, which is roughly when the problem started occurring for me.

 

[isladogs]

OK thanks. So 20266 is the minor build info for MEC.
In future, please report channel, version and build (both major and minor) as shown in the screenshot.
Also bitness where that appears to be relevant. Thanks

Out of interest, did you attempt a rollback to the previous working version?

 

[mellamokb]

So I have confirmed that downgrading to v1509 (19231.20246) from November 11 fixes the error. I have also confirmed on a 3rd machine that upgrading to the v1510 build gives the same error trying to make cross-database DAO calls. On the 2nd machine where the Dev saw the problem a week prior and then the problem went away, we are not sure if it is the same circumstance, because that Dev has Access 2010 installed and does not have Access 365 app loaded (just the rest of the basic 365 suite). So it's possible they are not exercising the same code paths - even though the ACEDAO library being loaded by C#.NET is the same version as the failing environments.

One thing of note on the Monthly Enterprise release notes, there is an Access CVE regarding remote execution due to relative paths. It makes me wonder if they have removed the ability to use paths inside of queries via external DAO calls, because of a vulnerability. The last thing I have available to try is to switch temporarily to the latest Current Channel version to see if the issue is still there.

If this is intentional change in behavior, I guess we will have to start looking for a workaround.

 

[isladogs]

I assume you meant versions 2509 and 2510 respectively. It would help if you could supply two simple repro databases and (if necessary) a script as so far none of us can replicate this issue

 

[mellamokb]

Yes sorry I do mean v2509 and v2510. I have attached the files I used in the repro test above.

- test1.accdb - has tblItem1, and a static query reference to tblItem2. Expected to be located as E:\test1.accdb
- test2.accdb - has tblItem2, and a static query reference to tblItem1. Expected to be located as E:\test2.accdb
- dbengine example operation not supported.txt - This is a LINQPad script, so rename from .txt to .linq, and open with LINQPad 5 (C# script runner tool) to repro the issue.

**Edit** One other interesting thing I found - I installed the latest Current Channel version (v2511 19426.20186), and I noticed that when I opened the two databases (outside of a Trusted Location) I got a security prompt to Enable Active content the top, even though neither database has any VBA code or macros. Then I noticed when I tried to run the query that reads from the other database, I got the same "Operation is not allowed" popup message directly in the Access UI. I had to click the Enable security prompt, and then the query ran as normal. Even after doing that though, I still get the same error message when trying to run the LINQPad test script. If I put them in a Trusted Location path these popups do not happen within Access, but the LINQPad test script still fails.