Go to Top

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!

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

7 Responses to "Optimize SQL Server with Access on a WAN"

  • Cees Monden
    September 4, 2012 - 2:25 pm Reply

    Dear Juan,

    I am a veteran Access/SQL Server developer and fully agree on using ADP’s, however, Microsoft has with the introdcution of Access 2013 stopped support creating or even opening ADP’s (ade’s) and they have no intention to change their policy.

    Any suggestion ? as Pass Through queries are not supporting any parameters in stored procedures and ODBC is not a solution due to problems (performance) with BIG DATA.

    Cees Monden

    • Juan Soto
      September 4, 2012 - 6:19 pm Reply

      Hi Ces,

      I will be writing a blog post on that very topic next week, stay tuned!

      Juan

  • andrej
    February 8, 2012 - 7:56 pm Reply

    Hi Juan,
    I would like to ask you about ADP connection from Access 2010 to SQL. I had created ADP connection from Access 2003 (now migrated to Access 2010) and I was using the connection to SQL 2000. This connection had been working fine and fast. Now, I have migrated to SQL 2008 and my connection from Access 2010 to SQL 2008 take much more time than before. I have no idea why. Could you advise some solution? Thanks a lot
    Andrej

    • Juan Soto
      February 8, 2012 - 7:59 pm Reply

      Hi Adrej!

      Try the following:
      Does the 2003 ADP have the same issue with SQL 2008? If it does then the problem is with SQL Server.
      What version of SQL Server 2008 are you using? Is it Express R2?

      Thanks!
      Juan

      • andrej
        February 8, 2012 - 9:10 pm Reply

        Hi Juan
        the issue is almost the same, I have just changed the network library (there was DBMSSOCN in connection to SQL 2003) and now is DBNETLIB in connection to SQL 2008. The reason was that DBMSSOCN library did not work in connection to SQL2008. The version of SQL is Enterprise Edition x64 SP2.
        thanks

        Andrej

  • Giorgio
    January 26, 2012 - 3:14 pm Reply

    Hi Juan, haven’t ADP’s been deprecated from Access 2007 onwards?

    • Juan Soto
      January 26, 2012 - 10:14 pm Reply

      Hi Giorgio,

      Yes, ADPs are deprecated and I do encourage you to use alternatives, but I still use them with legacy projects and they still may be the best way to connect with SQL Server. It all depends on your needs.

      Kind Regards,
      Juan

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.