Query in Access or SQL?
The other day I was trying to link two tables using memo fields, (don’t ask why), using 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 suggested by the tool. For more keyboard shortcuts click here.
