Hi all, I have just moved 2 tables from my access back end into sql server. I already had 8 tables that I lookup data from in a different sql server.
When running local queries that use tables from both servers in access these seem at least 5 times slower than when they were local tables but when I open the link to the tables directly then open instantly. Same when running the code in sql server. They are almost instant.
So not sure what is causing the delay is it possibility that they are in different locations ?
Couple of my thoughts.. do I need to open and close the connection to the sql tables or will this be happen automatically?
Should I write out the code in vba instead of using the design views for queries ?
One of my tables which is causing the issue every morning has the data deleted, then new data appended this then has a query is run on this to update a local table. Should I change this to a temp table within access ? Then delete one I have done my updates
When running local queries that use tables from both servers in access these seem at least 5 times slower than when they were local tables but when I open the link to the tables directly then open instantly. Same when running the code in sql server. They are almost instant.
So not sure what is causing the delay is it possibility that they are in different locations ?
Couple of my thoughts.. do I need to open and close the connection to the sql tables or will this be happen automatically?
Should I write out the code in vba instead of using the design views for queries ?
One of my tables which is causing the issue every morning has the data deleted, then new data appended this then has a query is run on this to update a local table. Should I change this to a temp table within access ? Then delete one I have done my updates