Hello!
I migrated from Access front and back end to Access front and SQL back end. I started by hosting a local SQL server locally on my machine, and now I am ready to push it out to a hosted SQL server. We moved the back end to a local server on site to test with and I noticed some latency (20-30 seconds longer) when opening up reports. It wasn't that big of a deal, but then we pushed it to a hosted server and that time has gone to 2-5 minute load times or the queries just time out when loading the form.
First off I had no Access experience prior to starting this internship, and this application has been used for going on 4 years. I started doing some research and I had a question. It looks like when a query is run in Access the first thing it does is pull a copy of the table from SQL server and then runs the access query? When I go into a normal select query and run it it takes a noticeable time to load into datasheet view, and after creating the pass through query it is almost an instant load time in datasheet view.
The issue I am having now is that this query has criteria that is input from a form, a date range, and with the pass through query being pure SQL code I am curious if this is even an option to do.
I am open for ideas if doing a pass through query isn't the way to go to speed up these queries, or if I am interpreting how Access interacts with SQL server causing the latency. I appreciate any information that can be suggested or supplied, and I hope I put this in the right forum as it felt more like a query issue with access then a SQL issue.
Thanks,
EFox
I migrated from Access front and back end to Access front and SQL back end. I started by hosting a local SQL server locally on my machine, and now I am ready to push it out to a hosted SQL server. We moved the back end to a local server on site to test with and I noticed some latency (20-30 seconds longer) when opening up reports. It wasn't that big of a deal, but then we pushed it to a hosted server and that time has gone to 2-5 minute load times or the queries just time out when loading the form.
First off I had no Access experience prior to starting this internship, and this application has been used for going on 4 years. I started doing some research and I had a question. It looks like when a query is run in Access the first thing it does is pull a copy of the table from SQL server and then runs the access query? When I go into a normal select query and run it it takes a noticeable time to load into datasheet view, and after creating the pass through query it is almost an instant load time in datasheet view.
The issue I am having now is that this query has criteria that is input from a form, a date range, and with the pass through query being pure SQL code I am curious if this is even an option to do.
I am open for ideas if doing a pass through query isn't the way to go to speed up these queries, or if I am interpreting how Access interacts with SQL server causing the latency. I appreciate any information that can be suggested or supplied, and I hope I put this in the right forum as it felt more like a query issue with access then a SQL issue.
Thanks,
EFox
