Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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
Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en SQL Server

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores