Hi Guys,
We have an app extensively used in our organisation.
I am revamping it. I decided to move dozens of queries (ran one after the other in Marcos) to SQL Pass through Query using CTE. That worked fine.
I reduced the numbers of queries (and so the maintenance) by a ten factor ! and now they are dynamic and do not need anymore to retrieve data and have the risk of outdated data.
But I am facing another issue.
In this Access database, there are specific data that we do want to maintain only here. These are external data to our ERP. We do not want to add new tables in our ERP.
So I have queries that are mixing MS Access tables and SQL Pass Thru queries.
Executing the SQL pass thru takes 1 or 2 seconds.
Querying our MS Access table takes less than a Second
But mixing the 2 type take minutes (up to 10!).
I tried to put the result of our Pass Thru queries in a MS Access table. Then the query above (that was previously mixing MS Access and pass thru) takes about 2 seconds to open.
But I fall back in the outdated risk (issue).
Do one of you have an idea on how to optimize queries that mix SQL and Access tables ?
Thank you very much
Dirk
We have an app extensively used in our organisation.
I am revamping it. I decided to move dozens of queries (ran one after the other in Marcos) to SQL Pass through Query using CTE. That worked fine.
I reduced the numbers of queries (and so the maintenance) by a ten factor ! and now they are dynamic and do not need anymore to retrieve data and have the risk of outdated data.
But I am facing another issue.
In this Access database, there are specific data that we do want to maintain only here. These are external data to our ERP. We do not want to add new tables in our ERP.
So I have queries that are mixing MS Access tables and SQL Pass Thru queries.
Executing the SQL pass thru takes 1 or 2 seconds.
Querying our MS Access table takes less than a Second
But mixing the 2 type take minutes (up to 10!).
I tried to put the result of our Pass Thru queries in a MS Access table. Then the query above (that was previously mixing MS Access and pass thru) takes about 2 seconds to open.
But I fall back in the outdated risk (issue).
Do one of you have an idea on how to optimize queries that mix SQL and Access tables ?
Thank you very much
Dirk
