One of the most common migrations we are asked to perform is getting a company off MS Access and onto SQL Server Standard edition. I imagine right now, some of you are asking yourselves What’s wrong with MS Access? First, I’d like to talk about what draws people to MS Access, and then I’ll explain why the same people who the popular tool appeals to, need a database developer the most, if they wish to avoid problems in the future.
MS access appeals to the nontechnical business owner for a simple reason. Nearly no coding is required. MS Access, with it’s pretty drop-down menus, allows users to create queries, tables, even stored procedures without ever typing a sentence. Will they be the most efficient queries? No. But will you be able to fix their performance later, using those drop-down menus? Also no. The only way to fix the query is to have MS Access generate the SQL script and then use your SQL Server expertise to tune the query yourself, which is the exact skill you use MS Access to avoid accumulating.
With it’s limitation to a single processer and a single core, MS Access will never be the efficient data solution. It’s single-threaded, meaning it can perform exactly one task at a time, making it an absolute nightmare if you want to run any kind of online store (imagine logging onto Amazon and having to wait for your turn to click “add to cart” and then get back in line and wait again to load the “check out” page) but it’s also inconvenient if even two people in your company need access to data.
Fret not, however, for Microsoft came up with a brilliant solution to the innate efficiency issues for MS Access. The solution? Limiting users to 2GB of data. That is correct, if you want to store the same amount of data as is contained in a standard definition download of the 2001 film, Shrek, that is too much for MS Access to handle. Don’t even think about data in the size range of Shrek in HD. The capacity limitation is 1/5th of the one allowed for free MS SQL Express edition. The query can’t be slow, if there isn’t much data to collect.
The performance issues are only the tip of the iceberg for MS Access. For example, there is no automated backup process at all. To make a “backup” of the database, you must save a copy using the “save as” feature manually, and store it under a new name. Technically, you will then be working with the new copy of the database, and the original will become a “backup.” To make matters worse, both the backup and the original would then be stored on a local disk, meaning in the new work-from-home landscape, the data for MS Access is also one there-was-an-accident-involving-my-cat-I’m-so-sorry away from being lost to time.
Even ignoring the security issues innate in MS access and potential GAAP violations from a lack of user concurrency, any successful business will eventually be forced off of MS Access. If you plan for that migration from the beginning, or better yet, begin on SQL Server Standard edition, you will save your company time, resources, and potentially fines and embarrassment.
