Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

SQL Server doesn't work with Access? I beg to differ

I do post on Access forums when I can, in particular at’s SQL Server forum. Many of you know SQL Server and Access are near and dear to me and I even travel the Country to speak at conferences about the two, advocating for the two as a great business solution.
Recently I had the chance to recommend SQL Server over the web to a forum user, which prompted a severe response from one of the forum’s VIPs. I of course took exception to his comments and decided to respond with some great counter points.
You can find the post here but I’ve also copied it below for your review. What do you think about Access with SQL? Have you had a pleasant experience or do you have some nightmares to share?
My Response:
HiTechCoach does bring up some points I’d like to address in regards to Access and SQL Server.Your Development Skills will have a direct impact on performance
It’s not enough to upload your backend to SQL Server and expect all will be well with your application. In fact it may not be, as HiTechCouch states, Access may ask for too much data over the wire if the application is ill designed. It is for this reason I offer tips on my blog for optimizing the relationship between the two and advocate a revamp of your application to improve performance. You must look at every report, query and form and ask yourself, how much data does the user really need to see? Can you modify the row source property when launching forms and reports to only fetch limited row sets? Revamping the app may take a lot of time, but the rewards will be numerous in terms of performance. To learn more about SQL Server take a look at this post.

Optimize your SQL Server Database
Just uploading the database is not enough, you need to go through it and add indexes, constraints, time stamp fields and even change column types in order to optimize the database in SQL. Here again a knowledge of SQL Server is critical in order to leverage the engine in your design. For tips on how to design tables in SQL for Access take a look at this post.

Maximize the use of server processing
I encourage Access developers to learn TSQL and SQL Server in order to leverage the best of both worlds. I recommend to the extent possible having the server crunch through numbers and perform joins prior to fetching the data into Access. By using views and stored procedures you will offload processing time from Access onto the server, which will always be better equipped to handle most data situations better than Access. To learn about pass through queries that don’t tax Access click here.

Optimizing over a WAN connection
Using SQL Server over a WAN presents it’s own unique challenges. From record locking to query performance, you will need to optimize your code and objects to work over a wide range of Internet speeds. Here’s another post to help you get started.

Database Performance CAN be better with SQL Server
I can’t speak for HiTech’s experiences with SQL, but mine have been wonderful! After following the recommendations above I’ve seen BETTER performance with SQL Server over the WEB than with a local Access database on the user’s desktop! Think about that for a minute. The system performed better with a server located in another state than it did when it was a regular Access database on the LOCAL HARDDRIVE.

I’ll be presenting at SQL Saturday #79 on this same topic so anyone reading this living in south Florida is welcome to attend, and it’s free! To register please click here.


What say you on this topic?

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 Access Help, SQL Server

Deja un comentario

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



Quienes Somos 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