I do post on Access forums when I can, in particular at UtterAccess.com’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 Microsoft 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?