I have an SQL server and MS Access database in the same local network - 1 Gbit/sec. And a lot of tables in that SQL server are linked into MS Access database via ODBC. And a query in the MS Acesss database using that a lot of tables. It opens fast - few seconds. Then I link all those tables to another SQL server having same tables, but this SQL server is in Amazon virtual machine. Connection speed is also about 1 Gbit/sec. But same query opens VERY slow - more than 5 minutes. What is the difference between local network and internet ODBC and can I speed it up somehow?
The problem is your connection to that server in the cloud is NOT 1 gig/sec. It's probably 10x slower. And well, 10x slower is well 10x slower.
If some operation on your local network takes 2-3 seconds, it will now take 20-30 seconds.
It turns out much slower connection speed is well, much slower!
I mean, place a file on that cloud system, and then download it, and tell everyone here what is the speed you see and obtain?
So, at the end of the day, you have to start optimizing the application. As you stated, that system runs just fine when you on your local network, but does not when running over the internet to the cloud system.
And, much worse, is what occurs if the user is at home, and they are not using the work high speed connection? Now, things are going to run even slower, since not everyone can afford a first-rate internet plan. So if some home-workers VPN into the company network, then their connections will be MUCH slower then even what you are experiencing now.
So, you need and want to start optimizing some of the slower parts of the application.
For example, you NEVER want to just open a form without also having some restrictions on the number of records that the form loads.
You can still well use forms directly bound to a linked table, but you ALWAYS want to restrict the data being pulled into that form.
Say for example, we have a customer form bound directly to the linked table (that resides in SQL Server).
You can use the following:
docmd.OpenForm "frmCustomers",,,"QuoteNum = 123243"
In the above, then Access is smart, and will only pull one record out of the say 1 million row table, and thus even on a relative slow connection, the Access form should load rather fast. (only one record will come down the network pipe, and this is despite that we don't have a query, don't have a stored procedure, and the linked table is directly bound to the table with 1 million rows).
However, if the form (or report) is based on multiple linked tables, then the join occurs client side in Access, and often such queries will be rather slow.
The simple solution is to take the query source (the query the form is bound to), and simply paste that SQL into SQL SSMS and convert to a view. Now, in the Access client side, simply rename (or delete) the query, and then link to the SQL Server view. This simple approach will now result in stored procedure performance, yet no changes to your VBA code that say filters the form is required.
So, extra effort is required when working with a MUCH slower connection, and adopting views, and efforts to restrict the number of records you pull into a given form is required.
As noted, you can well continue to use forms bound directly to a linked table, but you MUST then ensure that you restrict records pulled into that form.
So, if the form's data source is one table, then restricting records when using the open form command (along with a where clause) will solve such performance issues.
If the query has multiple tables (joins), then move that query to SQL server as a view, and then link to that view (using the same name as what the query was client side). This simple trick will again solve much performance issues.