SQL Server Express vs. Access for small-business applications

When you're weighing you options in database applications, it helps to make a feature-by-feature comparison. This overview will look at the feature sets of the Express edition of SQL Server 2005 and Microsoft Access. I did my best to avoid including too many details for the categories I covered, so that you can compare the two products from a high-level view.

Cost
When searching for tools for small business application development, cost is always a driving factor. You naturally want the largest feature set possible with the lowest cost. Luckily, SQL Express and MS Access are both on the cheaper end of software tool spectrum.

SQL Server Express Edition: FREE! The SQL Express edition of SQL Server 2005 is free but requires you to register your product with Microsoft if you plan to implement a solution with the tool. Although it's a scaled-down version of SQL Server 2005, this edition still contains much of the important features of SQL Server. Even though this product is free, be aware that it's more complicated to build solutions with SQL Server Express Edition alone, as it does not include any built-in interface design tools. This factor will likely require more cost for development time.

Microsoft Access: This product is included with certain versions of the Microsoft Office suite (e.g., Microsoft Office Professional Edition 2003 and Microsoft Office Small Business Management Edition 2006) or you can purchase it separately. The idea is that Microsoft does require you purchase it in some fashion, which can sometimes be a deterrent depending on your situation. However, once you have purchased a Microsoft Access licence, development costs can be significantly lower compared to SQL Server Express Edition. That's because it can be easier to develop your own home-grown Access solutions with little programming experience than it is with SQL Server Express Edition.

Implementation
Once you've assessed your application needs and your budget, it is time to decide upon what functionality you require.

SQL Server Express Edition: This tool should be used for small- to medium-scale applications. This database engine is a scaled-down version of Microsoft's SQL Server database engine. This edition supports many of the more advanced features available in the full SQL Server version, such as stored procedures, views, functions, CLR integration, snapshot isolation and XML support.

However, this is a database engine only — no interface development tools are incorporated into SQL Express as they are with Microsoft Access. Any development for a front-end application will need to be handled through a development environment, such as C# Express, which is also free. In addition, Microsoft has created a nice Express version of SQL Server Management Studio, which is the tool for administering your SQL Server Express database engine. A complete listing of Microsoft's free Express products is available from Microsoft's website.

Microsoft Access: If your situation is fairly small-scale, such as entering contact information, and the number of users accessing the system is also small (fewer than five), MS Access may be the option for you. Access has built-in forms, reports, and other utilities you can use to build your own user interface for your back-end database tables. Most of these programmable objects also come with some nice wizards for the more novice users. Typically, the development time for such a system will be lower when implemented via Microsoft Access because the application to be designed is usually smaller application and because built-in tools are available.

Reliability
Regardless of the size of the application, reliability is always a major cause for concern. If the data isn't available, you can't use it.

SQL Server Express Edition: With this product, you enjoy the same backup and recovery architecture you would have with a full-blown SQL Server version. This allows you to recover to a point in time if necessary. This product also supports transactional consistency, which ensures that your data remains in a consistent state in your database. This product will have no problem accommodating many concurrent users accessing and updating data at the same time.

Microsoft Access: This product does not perform optimally in terms of reliability. I've had some difficulty in the past when multiple users were accessing the application at the same time. Also, this product provides very little in terms of transactional consistency, and the logging of transactions is not supported. Because of this, you can recover your database file only to the last good full backup of the database. I have also had several problems in the past with the Access file (.mdb) becoming corrupt, which can be a headache to fix.

What about security?
SQL Server Express edition implements the enterprise-level granular security architecture that is present in SQL Server 2005. This allows you to fully control what users can see in your database. Security is somewhat configurable in Microsoft Access, but in no way does it give you anything close to the capabilities that are available in SQL Server Express edition.

Conclusion
If your application will be small and will require only a few users accessing it at any give point, a Microsoft Access application may be the correct approach for you. It provides pretty good performance for small data sets at a small cost. However, if your application will require several users and a larger data set, SQL Server Express Edition will likely be the route to take.

With either tool you plan to use, it's important to know that there is no substitute for development experience. Microsoft Access is more user friendly in initially designing your application, but experience and design knowledge will ultimately be the determining factor in the long-term success of your application.