If you’re having a hard time convincing management to use Access with SQL Server, then use this post as ammunition to get your point across:
Reason #1: Security
I’ve walked into situations where new clients are storing their customer’s credit card info, (including the three digits on the back), along with their billing address, home address and other confidential information in an Access backend. My reaction was to immediately notify them via a certified letter and as their consultant, I recommended that they cease and desist from using Access to store such highly personal information, and instead migrate to SQL Server. Otherwise I’m outta there. The fact is people can and will walk out with your Access backend on a thumb drive. This cannot happen with a SQL Server. Plus SQL Server integrates with Active Directory, has native encryption, and other security enhancements, Access doesn’t have this.
LIVE From Chicago Thursday January 26th, join us for Access KungFu, click here for more details.
Reason #2: Reliability
When you have users using Access backend, they’re all sharing a single data file and essentially cooperating and trusting one other to share nicely. If there’s a network issue or problem with one workstation, it could compromise the data file. This simply does not happen with SQL Server, it’s not affected by PC or Access crashes and is a more reliable environment for your data.
Reason #3: Processing Speed
When it comes to raw computational power you just can’t compare Access with SQL Server. If done right, your Access application should sizzle, pop and process large query operations with ease. SQL Server stored procedures and views are just no match for Jet.
Reason #4: Backups
Have you ever tried backing up an open Access file? Or have you come in the next day only to find your file corrupted and unusable? You can perform backups of your SQL databases while users are in the app.
Reason #5 Free
SQL Server Express 2008R2 will let you store up to 10 gigabytes of data and it is free to use. Of course it is not entirely free if you have to purchase a server, (it can be installed on a powerful workstation too), or you have to hire a professional Access developer to convert to SQL Server, but at least you don’t have to pay for this awesomeness. Don’t walk, run and get your copy today.
Reason #6: Web Enabled Access Databases (Data in the cloud)
SQL Server is the only way you can use full blown Access apps over a WAN, VPN or the Internet. Sure, you can also use Access Web Services, if you only need a simple app. But if you need to use all of what Access has to offer you can’t beat storing the data in the cloud and using Access anywhere in the world.
SQL Server and Access, Unbeatable
Next time someone says you don’t need SQL Server for your Access application I hope these six points and the posts linked in the article will help you convince them otherwise.
LIVE From Chicago Thursday January 26th, join us for Access KungFu, click here for more details.
Hello, what about using Access with linked lists on Sharepoint services (such as Office 365)? How does that compare? The problem with Access and SQL is also that if there is not a live internet point then there is no access to data. Do sharepoint lists cater for this problem in a suitable manner?
Hi Marc,
SharePoint Access services has a limited feature set and may be worth your while, it all depends on your particular situation.
I prefer to use the full Access database and not a subset of features which is SharePoint, yes, you do need a full Internet connection at all times, but my experience that has not been an issue.
Kind Regards,
Juan
Hi Juan,
I have a doubt about “SQL Server Express Editions” and is: how many users are “recommended” as maximum, to work in a effective manner?
Thank you very much for your very valuable articles.
Hello Angel and welcome to my blog!
SQL Server Express 2008 R2 is limited to one gig of RAM, no matter how much memory is on the machine, and 11 gigs of harddrive space per database. There is no limit to the amount of users. In general that’s not an issue, since Access databases are typically designed for 25-1 users. Once you start adding more users however you will notice a slower response to SQL Server until it becomes unacceptable. When that occurs depends on your network traffic, what the users are doing, etc. If you do run into these kinds of issues then you really need to migrate to a full license of SQL or consider hosting, which we offer.
Hope that helps!
Juan
Good day Juan, in mssql 2008 r2 express 10gb limit, how many number of rows or records can be stored? if it reach its limit can i attach it directly to a full blown sql server? any update about the new ms access release? in your own opinion why ms will not create a compiler for msa like in visual fox or rbase? is ms afraid that msa will compete with some of their db dev’t tools? thanks and more power
Juan,
This is a great blog. I found it while searching for answers around the ability of SQL Server Express 2008 R2 to work as the back-end of a website. How can I calculate when SQL Server will be slow? Is there a way to calculate RAM used by User Stored Procedure per run to compare it against the 1Gb of RAM limitation? Is there a way to estimate that, if the database is 5Gb big, then the remaining 5Gb of hard drive is all that remains for tmp storage of tables (used each time a visitor sends a request)? Any help is appreciated on this front.
Thanks!
Lucas,
Thanks for your kind words! We put in a lot of work into our blog and comments like yours make it worthwhile. In regards to performance and memory utilization I can give you generalizations but honestly it’s hard to tell how a procedure will work better: with temp tables or table variables, etc.
What we do at our firm is test it both ways: when we want to determine if performance can be improved with another technique we actually go ahead and do it. This is particularly more relevant with SQL Server Express and it’s limitation to only one 1 gig of RAM and 1 CPU.
Hope that helps…
Juan
I have migrated all of the tables in my Access application to SQL Server. In Access, I frequently view the data using an Access form in table view with auto-filters turned on. I like the way Access would always provide a list of each data instance in the filter list and I could just put a checkmark beside the example if I wanted to include it in the filter. Now that the data resides in SQL server, the filter examples are not there and my only option seems to be to enter a text search string. Any workaround for this?
JW
You may wish to consider using drop down lists at the top of your form to assist in filtering or creating a search form, either approach will mean a lot of coding.
Kind Regards,
Juan