Data Management

Should you use SQL Server Express Edition or Microsoft Access for your small business applications?

Small businesses that are considering adopting a database application have a variety of choices. IT pro Tim Chapman focuses on two of them--SQL Server Express edition and Microsoft Access--and discusses how they stack up in terms of cost, implementation, and reliability.

This article is also available as a PDF download.

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 least amount of 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 license, 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. For a complete listing of Microsoft's free Express products, click here.

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 backend 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.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

32 comments
StampMan
StampMan

I am new to learning SQL, and would like to know if there is a online site that have free tutorial that i can follow using MS Access 2007.

marleng
marleng

I was honestly scared about making the move to SQL, I knew that I need something with more options to keep track of all of the business reports yet I was not sure that using any kind of "fancy" reporting system was going to be worth the cost. But I'm very glad I did, it has help everything within the business move allot smother specially with multiple collaborators, is saves me time so I can work on other important thinks. Plus its easy to get help on using the SQL, I just call Reporitng Guru and get a nice easy solution. So if you are scared of SQL don't be!

the_grove_man
the_grove_man

This puts features of a web driven application but what about a desktop application? I have never seen a comprehensive overview of why SSEE would be chosen over Access in this capacity. (When I say Desktop, I am generically assuming the app will be used, one user at a time) like say Micrsoft Word. Can we see that comparison?

alexp023
alexp023

I've read a lot here today and a lot of good points were brought out. But nothing speaks better than experience. I've used Access for a back-end for many years and many times I wondered if I should be using something else. First MSDE, and now SQL Server Express. Keep in mind that I use VB as the front-end and these are Windows forms apps. I have many apps running Access, up to 15 users at one client, with absolutely no degradation and I have had only positive results. Most apps track between 5,000 and 10,000 vendors / members / clients but have transactions well over 100,000 records. Again, no problems. It is reliable, easy to use, user-friendly, etc... Side-by-side, for me, it blew MSDE away. My only reason for looking into SQL Server Express right now is because it's the thing to do. I'm now in the process of building 2 new apps. One will be Access. I haven't decided on the other one yet but I will probably give Express a chance. I could always easily move it to Access. If Microsoft hasn't, don't be so quick to give up on Access.

CodeBubba
CodeBubba

Jody, I don't know if you've discovered this or not - but Microsoft now offers (for free) the front-end tools for SQL 2005 Express. I just downloaded and installed them. You get SQL Enterprise Manager and the online books too ... all for free. I presume the only limitation is the number of concurrent users on the development machine - like MSDE has always limited you to - but the limitation of no management tools for it no longer exists. Regards, -CB :)

Tony Hopkinson
Tony Hopkinson

If you are going to use access as the development tool and it's always going to be a small number of users and a small amount of data and it's not that critical, then OK access is a choice. A bad one, but possibly a practical one. If you are using access to be mobile, ie carrying the mdb about locally and then resynching with a main database, or passing it between users without a constant connection, then mobile is now a much better choice. If you are using it because it's called a database and you got it free with office, then you are doing yourself no favours at all. As an investigation tool for power users, preferably off a copy of the live data, it still has some attractions, to write professional systems with, total non starter. The real nightmare is when your requirements outgrow a desktop database, porting it over to a more full featured one, you either end up with a really crappy solution, or you have to rewrite from the ground up. There are other free DBMS's that don't come from MS of course. So it's not one or the other.

onbliss
onbliss

Access as a front-end and SQL Server as the back-end is just another option in the mix. This allows us to utilise the ease of developing forms and reports in Access and keep the data in SQL Server.

JodyGilbert
JodyGilbert

Have you worked with both these products? Which one comes out on top in terms of meeting your needs?

Tony Hopkinson
Tony Hopkinson

is it tends to be the first database newbies use. So on top of it's own inadequacies when it comes to big systems, you get very juvenile code, such as lots of client side processing, business logic in the UI etc. As for going from express to access, it depends on what features you use doesn't it, I must agree if you can move it easily, you probably didn't need it.

reggie.benito
reggie.benito

as i've mentioned in my reply title, i agree with you 110% on the topic you've just discuss and other topics being brought up. being an access user myself for years now, i find myself trying to learn new tricks on how to use SQL Server 2005 Express Edition. but let's not forget what the thread is about. obviously the guy still don't know how to use SQL and/or Access hence the topic. if you're starting with a small project and will be using it for quite some time, i would suggest you use Access until such time you'll find it to be sluggish or so. i guess by that time you've already familiarized yourself with SQL Server (be it Express Edition or not). just my 2 cents.

Tony Hopkinson
Tony Hopkinson

There's some enterprise level functionality missing such as SIS (new DTS). MSDE didn't limit number of users it was choked in terms of the number of concurrent batches. You should also bear in mind SQL 2005 is significantly and in my opinion disasterously slower in terms of processing ad-hoc queries, prepared or not. It's still faster than access though.

gsquared
gsquared

Upgrading a small Access database to SQL server is as simple as firing up a wizard and running it, in many cases. The DB engine used by Access since Office 2000 has been T-SQL "compliant" to a very large extent. That means upgrading tables, views and stored procedures from the Access database to MS SQL is very, very simple in most cases. Of course, the front end has to be modified a bit to connect to SQL instead of an Access database (either in the same file or separate), but the wizard in Access does that part for you too, if I remember correctly. (My big issue with converting from an Access database to an Access front-end on an SQL server database was that the original database was badly designed. Too many wide tables with multiple columns for things where I should have had subtables. I know better now.)

onbliss
onbliss

You say it can be bad or "doing yourself no favours at all". Can you please elaborate? I have worked on different applications: VB6 connecting to Access, Access app connecting to Access database, Access app/mdb connection to SQL Server, Vb6 connection to SQL Server plus applications not germane to this discussion. Each case is different, and had pros and cons. I have seen ETL logic written in Access 97, and then subsequently migrated to DTS in SQL Server. It is certainly feasible and cheap to design and port the database to bigger databases. It requires good understanding of the system and simple software development principles. Rewriting per se is not a bad sign of the state of software. And crappy solution can be implemented even in SQL Server. Once I had to work (actually clean up) on an Access database that originally was started by a person who had very little experience in designing and creating database/applications. The person had picked a book from a store and started creating what was needed (because the IT department thought the application was too small to create and support; and hence had assigned a low priority). Well, sadly the tables were not created properly and I had tough time getting it into shape. I could do only so much. So yes, it is so easy that people can get started on it and create maintenance issues. But the database had served well for about 15 people until they needed a professional to help them out.

C_Tharp
C_Tharp

Using Access for the front end provides the tools for rapid and flexible reporting. ODBC provides the connection to the back end database. SQL Server provides integrity and multiuser access, among other features. The worst limitations of Access are avoided. With this modular approach, either piece can be changed to meet future needs. The front end can be replaced without affecting the database. The database can be replaced with little impact to the front end. We use Access with an enterprise Oracle database. The database is part of an application that provides all of the data entry, analysis, and management. Access provides report flexibility that is lacking in the application.

TasmaniaBill2
TasmaniaBill2

Not only "just another option" but a good option at that. It has worked quite well for me.

srinu57
srinu57

Access is limited to desktop use, MS SQL Server Express can be used over the N/W usimg TCP/IP. So you can use your database for networked applications also. Hence migrating to SQL Express is recommended.

gsquared
gsquared

If you're talking about a project/business that has access to a person who knows how to "database analyst" (to mis-verbify a noun), then I'd definitely have to go with using SQL (or whatever other free database solution the DA is familiar with). Here's how I got into databases and how it worked for me: I was in sales and marketing for a small business. I was one of two salespeople in the whole company (which had, if I remember correctly, about 15 employees, tops). There was no means of keeping track of customers and orders, except when an order was placed, it would be put in a manila envelope and kept on someone's desk. So I started an Excel spreadsheet to keep track of my own customers, their contact data, their order history, etc. The spreadsheet rapidly became too large and complex for easy use, so about a month after starting that, I decided to check out this "Access" product. I'd used flat-file databases before, at prior employers, but no relational databases, and wasn't even sure what "relational" meant. Access was very easy for me to learn and get some decent use out of. Took me a couple of weeks of playing around with it between sales calls and such, and I had something that would work for my needs. By this time, we were up to six salespeople, and the other five all wanted to use my Access app too, as they saw me using it. So I struggled through the help files and, again in very part-time work, got it set up so the database was on the server and the front end could work from any workstation in the company. Functionality expanded. Features were added. Management wanted access to the data in it. The production and delivery departments wanted access to the upcoming jobs stored in it (what we sold, they had to produce and ship). Payroll wanted access to it for commissions data. And so on. Rapidly, it became clear that too many users made it unstable and we were running into concurrency issues routinely. So we upgraded to SQL Server. By that time, I had a good idea on how a relational database should work (as opposed to how I started out) and the migration to SQL took only a couple of days. It's been five years since I started out trying to build something to keep track of my own customers, and it's been 3 1/2 years since I actually had any customers of my own to keep track of, since I was moved to full-time system development. The application still has an Access front-end, the back-end is SQL Server 2000 Enterprise, and we have some very feature-rich .NET Web pages on the same database (though I don't do the pages). I'm in the process of working out how to move to SQL 2005 and a .NET front-end (probably C++). That kind of path, starting with easy-to-learn Access, moving to SQL when/if the application grows in use and functionality and importance, is very good when you don't have someone available/affordable to build a "real database" right off the bat. Also, if the database never ends up being mission critical, and stays small and easy to maintain in Access, you've invested less time and effort. After all, experienced database analysts are much more expensive than having someone in Sales spend half an hour building something in Access.

AJ-Ubuntu-User
AJ-Ubuntu-User

It is also worth considering Mysql if you do choose to use a SQL-server solution within a small business. It is free and the amount of support and pre-built app's available makes it a very sensible choice.

chapman.tim
chapman.tim

In the past, I have found that Access works rather well for small business that are capturing a small amount of user data. It also works well in terms of reporting for the data...the user interface for designing queries abstracts you from having to know SQL. However, in terms of features, scalability, and stability...SQL Server is the winner hands down. It has so many more features to scale your business.

john.a.wills
john.a.wills

What is a multiple user? I suspect you meant "several users" or "multiple users" or perhaps "several simultaneous users".

Dr_Zinj
Dr_Zinj

You're absolutely right that it works well for small groups of people (5 concurrent, 15 or 20 or so total), and for small to medium amounts of data. The biggest bit of advice I can offer to non-DB professionals building a db in Access is the same one that many professionals forget about: DOCUMENT EVERYTHING. If you've documented everything correctly, you know what the relationships are, what all the properties are, what exceptions are allowed, and as importantly, WHY they were built that way. Not only does it make maintaining and modifying the database easier (and being able to write reports out of it); but is also makes a good base for those situations where you have to upgrade to a bigger database engine - you still may have to design from scratch, but you've got almost a complete analysis to work from then.

Tony Hopkinson
Tony Hopkinson

and you didn't need the portability aspect, why are you using it. It's probably me but I've seen a lot of access based systems, that someone tried make into a complex business application and failed extremely badly. If you have plans for the data volume, the number of users or the number of uses to grow, then starting with access is a mistake. Like all design errors, you need to discover them early. I've seen people try to get concurrent access :D for fifty users with gigabytes of data and entire swathes of business and storage logic wodged into the presentation layer. I freely admit it put me off.

mdv3441
mdv3441

This solution .mdb or .mde will expose the UserID/Password(s) of your SQL Server to binary editors. Not usually diserable.

CodeBubba
CodeBubba

Same here. I really like the "Documenter" that comes with Access. I use that to generate those nice little table reports describing SQL Server tables. Odd that SQL never came with something like that. Nevertheless, it works great. -CB :)

chapman.tim
chapman.tim

Sorry, I wasn't able to find where you were referencing "multiple user", but I would have meant "multiple users" in any case. Thank you. Tim

JodyGilbert
JodyGilbert

A veritable plethora of several multiple users! Thanks for the catch; fixed now. Jody

onbliss
onbliss

"Had a 'debate' with one guy who uses it as back end for web server systems." That I wouldn't do, unless as you say if it is going to be used by only one person :-) Even if it was just a handful of users, I would have gone with MSDE (now SQL Server Express edition).

Tony Hopkinson
Tony Hopkinson

Your CD collection, asset log for a one man shop. Some weekly performance graphs, it's a good tool. I've seen it used well in professional systems as well. Had a 'debate' :D with one guy who uses it as back end for web server systems. He called that 100s of users, in actual fact it was one, but the guys on the other end don't know that. He also called it big (100M) , I guess I'm a bit of a size queen, I've worked on tables bigger than that. :D Scalability is an overarching requirement in the environments which I work, Access just doesn't cut the mustard on that front, and it never will and still be Access.

onbliss
onbliss

As a developer predisposed to using Access only as a database I would not use it as a front-end. Since, I can code and create database, I would naturally go for C# and SQL Server solution. But sometimes Access is useful for a quick solution. I am talking about users who do not have enough experience in creating applications; but who just need something to get their job done. I am sure you will agree that all IT does not run on the BIG systems, eventually the small Perl utility or the Excel sheet or the Access database is used by the end-users to accomplish tasks. I have seen 20 users trying to use Access concurrently, and run into problems. I have read at Experts-Exchange.com people claiming they have had no problems with 50+ concurrent users. It is about how they split the app and mdb part, I guess.

mdv3441
mdv3441

Yes, I did omit that fact. I just wanted those that use SQL security to know that Access front-ends are not necessarily a good idea.

gsquared
gsquared

You can use Windows security on the Access-SQL connection. Then there's no userID+password stored in the binary. Of course, that requires that you have a domain or be running on the local machine.