Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

SQL Query vs. Access Query – Pros and Cons

The other day I was trying to link two tables using memo fields, (don’t ask why), using

Query in Access or SQL?

an Access query, that’s when I realized it can’t be done in Access. Since the data was hosted in a SQL Database, I tried the same thing in a SQL query using SQL Server Management Studio, (SSMS), and viola, no problemo. That got me thinking about this blog post, what other differences are there between Access and SQL when creating queries? Under what conditions would you use either?

In general use SQL Server Queries
It may be a no brainer but has to be said, if you can do your query in SQL Server than do so, period. Here’s why:

  • Execution plans: The server will always be quicker than Access in churning out results, plans for frequently used queries are stored in memory, providing fast access to requests from Access
  • Security: Views can contain other views for which the user has no security rights, providing you with a wealth of options to serve up data under certain conditions the user may not have.
  • No roll-out necessary: I base all of my reports as much as possible on views, why? If there is ever a need to change the criteria or «tweak» the query, but still have the same output columns, I can just do it on the server and viola! Instant roll-out to my Access clients.
  • Unconventional Queries: As I mentioned at the top of my post, things that you may not be able to do in Access may be possible on the server, joining on memo fields is one of them.  (I did it as part of an import operation, not advocating you join on memo fields in general).

When to use only Access queries?
You may think there is no room for Access queries in your life, but here are some reasons why you may still consider using them:

  • When your working with a tyrannical SQL Server DBA who would rather die than add a new view designed by you to his precious database.
  • Combining SQL tables with local Access tables in a query
  • Make table queries
  • When you need a quick temporary query for analysis. I develop in Access and usually have a SSMS window available for immediate results, but sometimes I’ll just do it in Access to
  • When you’re using custom functions, TempVars or other Access specific techniques. Let’s face it, if you’re reading this blog you may be more familiar with Access then SQL, although most Access stuff can be replaced with SQL Server specific functionality, you may just be better off doing it in Access.

Don’t forget to optimize your query!
You can view the execution plan in SSMS by pressing CTRL-L and adding any missing indexes to your tables that are suggeted by the tool. For more keyboard shortcuts click here.

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

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