Optimize SQL Server with Access on a WAN

I recently responded to a post on UtterAccess.com regarding how slow his Access database was performing when the SQL Server was located in another state on the same WAN. I’ve reposted my response here with some additional info since I believe Access with SQL Server is the secret sauce needed for almost all Access applications.

To optimize you’re going to have to dig in and make sure you have the right views, indexes and keys in your database, as well as optimizing your code. It’s a lot of work but very rewarding, in fact I’ve been able to tune Access and SQL Server over Wan were the performance was BETTER with SQL then with an Access backend located on the local harddrive!

Here are some pointers for you if you’re not using an ADP file, then I’m going to end with a much more labor intensive suggestion later:
o Consider using local tables for data that rarely changes, for example, the list of state in the US. Many of these static tables are used for filtering and all of them should be on your frontend. You will need to add code that will update the local tables when there is a change, one approach is to do so on startup, where you can trigger a download by flipping a switch on a SQL table.
o Maximize the use of views on SQL Server instead of having Access doing the querying on the front end. You can then use the views as a recordsource on the form. To edit the data you will need to add index in Access and maybe a delete trigger in SQL.
o Make sure you’ve created foreign keys on your tables.
o Don’t load data when your form opens unless you must. Instead strip the recordsource property and require users to select a filter on your firm, then populate the recordsource property with your filter.
o Use just one connection in your code to perform ADODB operations and minimize the traffic to your server.
o Create views for all your reports, never have Access do any client side querying if you can avoid it.
o Optimize your views and SQL queries by using query analyzer, (Ctrl-L) in a query window in SSMS. You will need to copy the SQL statement from your view to a new query window, perform the analysis and then create any suggested indexes. This technique alone will improve operations immensely!

ADP is the way to go
ADP is the optimal combination of SQL with Access! There is only one connection being used, it’s designed from the ground up to work with SQL Server and in many cases it will be faster then a local Access backend. If you do go the ADP way prepare to work over some hurdles:

• No local tables or storage
• Login issues
• Getting SQL Stored Procedures to work as a form’s recordsource can be tricky