Go to Top

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

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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?

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

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

  • 10 things every Access developer who works with SQL Server should know | accessexperts.com
    May 14, 2013 - 9:37 pm Reply

    […] Using a pure Access solution, (no SQL Server), is almost impossible over VPN, but not a problem at all with SQL Server as your backend. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.