Access 2010 to 2013 UNION ALL (1 Viewer)

[smbrr]

Hi

My company's moving from Windows 7 to Windows 10.
Additionally, don't ask me why, they decided to move from Office 2010 to Office 2013.
During UAT some problems came up.

I got a huge UNION ALL query that takes a dozen linked tables and mash them together on some criteria, on Office 2010 it takes 200 seconds to run, and on Office 2013, it takes 2000 seconds.

Is there something I should know about Access 2013 and/or Windows 10 regarding this?
The other queries seem to run fine.

Thanks

 

[Ranman256]

There should be no difference. SQL is the same in all versions.
Now, it's possible a link may have broken,
Or a function reference got broken due to the upgrade.

Check VBA references: alt-f11, tools,references. If anything says MISSING,then that's 1 prob. Find the new version in there.

Rebuild the Union using individual working queries,
Select * from Q1
Union
Select * from Q2
Union....

 

[The_Doc_Man]

I got a huge UNION ALL query that takes a dozen linked tables

From the same back-end file or from multiple back-end files?

There were a few changes to Windows regarding file locking and network security but I'm not sure about them. We were in the process of upgrading (?) Win7 to Win10 when I retired and the system admin who was working on reconfiguring the out-of-box Win10 to meet U.S. Navy standards had to get a short haircut to prevent himself from ripping it out by its roots. I didn't stay on the job long enough to get the bloody gory details but my old buddy J. N. was emphatic (euphemism for "loudly cursing") about the file system security issues.

Another question: Regarding the file or files wherein these dozen tables exist, have you set up a persistent connection from the front-end to the back-end(s)?

 

[smbrr]

Checked all the references, refreshed my linked tables, no issues.

All the tables are from separate files. When opening the front-end, I have "dummy" tables in each back-end being opened to create persistent connection.

I tried rebuilding this union query because it had no subqueries and a "in (SELECT ...)" clause that isn't best practice. I made a bunch of subqueries with proper inner joins on tables instead. No improvement.

That union is also an INSERT INTO query, I tried by making it a select, no difference.

Tried all this multiple times and with a compact between each.

So I don't know, I tried. I'll just have to leave it like this, I guess. The application is way to complex and little used to rewrite from scratch.