Software

10+ reasons to use Access (and a few reasons not to)

Microsoft Access may not be the king of databases, but it does offer a number of advantages over solutions like SQL Server and Oracle in certain situations. Access guru Susan Harkins counts the ways.

Microsoft Access may not be the king of databases, but it does offer a number of advantages over solutions like SQL Server and Oracle in certain situations. Access guru Susan Harkins counts the ways.


Nothing starts a fire quicker than saying, "Hey, let's use Access. Yeah, Access can do it!" Oh my... just thinking about it makes me want to don a fire retardant pocket protector. Seriously, though: Access elicits real passion. Developers and IT folk either love it or hate it. There's no reason why you should use a tool you hate, but you should strive to use the tool that's best for the job.

The truly smart and effective IT professional knows that there are many tools. The key is to know which database engine is the best for the job at hand. Why throw massive resources at a simple need? In other words, you don't want to pay for a Rolls Royce engine if you're building a go-cart. Knowing when and when not to use Access will help your budget and make you look good, whether you're a freelance developer or you're managing IT resources.

Note: This information is also available as a PDF download.

#1: It's cheap

Access is a desktop database and its place in the hierarchy -- layered between Excel and SQL Server -- determines its price. Access costs the same as any other desktop application. Acquiring a copy of Access won't require a loan or a call to investors.

The large client-server databases, such as SQL Server and Oracle, require specific hardware and expensive licensing. After the project's up and running, a client-server database needs a dedicated administrator. Access requires none of that.

On the other hand, Access is a desktop application. That means that everyone who uses a customized database application needs Access installed on their local system. All those copies of Access can be expensive. One alternative is to invest the time and money it takes to turn your database into a runtime application. (Access 2007 doesn't support this option.)

#2: It's easy

Anybody with just a bit of time and reasonable intelligence can learn to use Access. It doesn't take weeks of classroom instruction and then months of mentored on-the-job training to acquire the skills necessary to create and administer a database. It's safe to say that most Access databases have one user and they live out their lives on one system. The user generally creates the database in his or her spare time. The casual user with no professional database or development skills can get data into an Access database and then manipulate that data without blowing up the building.

A good database grows and a bad one dies -- regardless of the data engine that's driving it or the skill set of the person who created it.

#3: Development costs less

Many developers make a good living creating custom database applications in Access. (Call me, let's talk.) However, in general, they charge less than SQL Server and Oracle developers. Moreover, the development costs are just the beginning if you go with SQL Server or some other client-server software (see #1). If you really want to use Access and you're smart, you'll see that an enthusiastic and eager employee gets the right training. Then, pass out all that money you save in employee bonuses.

On the other hand, it doesn't matter how much money you save initially, if you use the wrong database. Don't let money be your only consideration or you'll surely regret it. For instance, the security model is minimal (and doesn't exist at all in Access 2007). Recovery isn't as easy, either. Don't use Access for mission-critical applications unless you really know what you're doing -- and even then, it might be a good idea to keep your resume updated.

#4: Prototyping is a snap

Access is a great way to show fast results for the impatient client or boss. You can collect a little data and in just a few hours (or days) wow them with a few neat forms and reports -- I can hear them ooing and ahhing already. You don't have to use Access to build the production database, but you can ease client concerns by showing that you understand their needs. Access lets you get results fast and often with little to no code.

#5: It's easy to upsize once it outgrows Jet

People who control the purse strings aren't usually willing to dedicate resources to developing a noncritical database. Most of the time, you're on your own. However, that doesn't mean that a good design won't grow and evolve into a truly useful tool. If that happens, you can upsize an Access database to SQL Server. You don't have to start from scratch.

Still, Access is limited to 2GB. Even if the database's purpose isn't critical, the amount of data alone might force you into the arms of a more robust engine. Realistically, you probably won't run into that limitation too often. If you do, you can eliminate Jet from the picture and use an Access front end to link to SQL Server data.

#6: It's a one-time fling

Not every custom database has a long shelf life, but that's not because it's bad and dies an agonizing death. Sometimes its purpose is timed. For instance, generating, collecting, and analyzing questionnaire data can be a big job, even for Access, but a single questionnaire has a limited lifespan. If you're going to use a database once, or for only a short time, use Access if possible.

#7: It can provide a quick fix

The best solution for your needs might be a powerful client-server database such as SQL Server. However, while you're waiting -- and you will wait -- how's the work being done? You can use Access as a quick fix until the more robust version is ready. You'll have to compromise, because if you really need SQL Server, you're not going to get the same work done in Access. But you might get portions of the work done. Analyze the overall tasks and see what components you can automate in Access, at least for the time being.

#8: You want to change what?

Access is flexible, and that's one of its best attributes. Even if you can put a custom database together in a matter of weeks, needs are likely to change. Almost immediately, the user or client will think of something they want to add or change. If you designed the database well in the first place, Access will handle enhancements and changes without complaint.

#9: It talks to Office

Access is part of the Microsoft Office suite, so it plays well with the other applications. Users can quickly and easily export data from or import data into Excel or publish reports to Word. In addition, it shares a similar interface with other Office apps, which helps new users feel more at home and diminishes the learning curve.

#10: There's less code!

All things being equal, Access can get the job done with less code than SQL Server (or some other client-server database). In addition, VBA is an easy language to master.

#11: It offers connectability

Access offers an affordable solution for individual users and smaller teams. Despite protests from some member of the IT club, you can even use it across a network if you know what you're doing (file server solutions on a local network).

On the other hand, Access isn't optimized for the Web. Although a skillful developer can use Access on the Web, in general, it just isn't a good idea. Jet can't handle large numbers of simultaneous users, unless of course you really know what you're doing -- and that level of expertise is really closer to magic than development. It can be done, just not by many.


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

22 comments
vernonhorn
vernonhorn

I agree. People diss Access all the time, but for small, low volume projects, I've had great success with it.

Snak
Snak

What is the problem here? I have created many applications using Access (in fact, one of our most critical applications is primarily an Access database, which has been doing a very complicated job for 12 years). Using MySQL it is easy to create web-enabled databases that can be administered by an Access front end. I have several applications on the go that use a web front end for remote data collection and reporting, a MySQL back end and an Access interface for administration. The beauty of this is that a reasonably competent user can add or amend queries and create ad hoc reports. It's true that the tool should suit the job, but just because Access is seen as a 'desktop database' does not mean it cannot be used to provide a stable solution to a complicated data processing requirement.

chewingun1
chewingun1

There's no bug fix support since A97. Many old bugs still survive, even in A2007!

dsomerv
dsomerv

Microsoft offers free runtime and packaging software so it is easy and cost free to take an Access2007 application to any windows PC ,i.e.: it is NOT necessary to buy Access for each PC as you state in #1.. For more info : "Access 2007 runtime" into the Google box.

angeleyes
angeleyes

In business it comes down to cost, and not just the cost of the software. There are the costs of migration, then training (including the trainer, the class space for the trainer, the time of the employees who will need to be clocked in, the loss in time those employees would normaly be spending performing their regular tasks if they are being trained during their normal shift, training for indivuduals that missed the first session(s), etc...), the hardware (if you need to upgrade the server hardware or server software for some reason or plain just purchase a server), the software that will connect to the database or paying for someone to .Dev it, by the time your done your looking at a possible cost overhead in the thousands for your free software. No matter what you like or dislike, I agree that the tools should fit the job. You wouldn't try to build a house with just screw driver after all, and if you are... find another day job the current one won't be there for long. So match what will match the needs and costs of the project, not the whims of an opinion. Access is by far and large one of the most easy application databases to use, unless you have been in IT or keep up with IT ins ome way most people are going to ask what this access program that came bundled with MS do the research and figure it might work for thier database needs. For home to small and maybe even medium sized (departmentalized) business use access is going to fill that need, wont' cost a penny software wize as most every company in the world is using a MS Office that comes prebundled with it, no download or setup necessary just the .Dev part. Access is also a great way to prototype a database prior to putting it on a different engine such as mySQL or SQL. I have dinner to go eat or I would keep going, let me just leave my comments here... Just because it works, doesn't mean you should use it and deffinatly doesn't mean it will be cost effect. Just because In Your Humble Opinion it's the best doesn't mean something else will get the job done better and be more cost effective. There are hundreds of solutions out there for a reason, and it's not just because one is better than the other.

CarlosHawes
CarlosHawes

Gee, if reason number 1 (cost) is the main driver, you may want to consider mySQL. It too is cheaper than Oracle or MS SQL. As a matter of fact, it's FREE. It also scales much better than Access for multiple user apps, it basically runs forever without crashing, it is available on any Operating System, not just Windows, and you can use just about any programming language with it. I really don't understand what niche ACCESS is really good at filling anymore.

maxmawali
maxmawali

After a few rereads of your article, I find I have to respond - The 10 reasons by heading are flawless but from point #3 on, the article gets progressively not committal and even apologetic - especially seeing as you develop with access and you're writing for "TECH"-Republic! Then there are issues that need clarification: First you say "Many developers make a good living creating custom database applications in Access. (Call me, let???s talk.)", and then go on to say "Don???t use Access for mission-critical applications unless you really know what you???re doing ??? and even then, it might be a good idea to keep your resume updated." The latter statement cannot be put down to a matter of opinion and is pure bull! It surely results from a lapse of concentration on your part (either when writing the article or when and if you made the big boob00!). I suspect that an article of this nature must have some thought (and action) inspiring punch, or be left for a forum or article where brevity isn't an issue - this is Tech-Republic after all, and not "10 easy ways to start a business for laymen"

jdowski
jdowski

Susan, while I greatly enjoy reading your articles on Access this topic seems to have been talked to death in just the few months I've been a member on TechRepublic. Access has no greater proponent than myself but I work as an analyst attached to a business. Most folks in my position will also speak highly of Access's (sp?) power and flexibility. Folks who function in an IT role will will tend to look down on Access for precisely those same reasons....which to a certain point I can understand. Access, like any tool, can be utilized intelligently or...uh, not so much. Talented or not so talented analysts will develop solutions in Access and then move on to other opportunities. This may leave the business with a now mission critical app that they may no longer be able to support internally. They look to IT to at least maintain the stability of the app. IT bristles at inheriting responsibility for an app that may be poorly designed and/or executed and also most likley with little documentation. I have always thought that it would be wise of most IT dept.s to have a couple dedicated Access folks to either develop Access based solutions for businesses or at least be available to businesses to help their analysts develop smart, scalable, standardized (as much as possible), and documented solutions. I look at it this way....folks are going to use Access with IT's blessing or not...might as well be involved from the get go to ease the pain as well as add value. What's the alternative ? Take away Access altogether ???....so mission critical apps will be developed in Excel ???

Tink!
Tink!

I agree that there are many instances where Access does not fulfill the needs. But I have found many instances where it does. Being that I've always worked for smaller companies, I've been able to create database applications that fit what they need or were looking for to the "T". In fact, I am currently working on expanding the functionality of a current Access database that helps track raw materials. [i](yes, I know...if I'm working on that what the heck am I doing on TR right now during work hours.) [/i] :p I must say that VBA is a very easy language to learn to manipulate. Especially if you already have some BASIC [i](oooh..play on words!)[/i] knowledge already. :D [edited for grammar]

CarlosHawes
CarlosHawes

With a background as a DBA, I find the ACCESS user interface to be cludjy. It is so GUI centric that it is hard to really get at the meat of the database itself. It is like you design the app more so than the database. The way they taught it to us DBAs was that you first design the Database itself (often from a command line) with all app logic baked into the database with stored procedures, constraints, and triggers. The choice of application language is then immaterial as long as it is ODBC compliant. As a matter of fact, it is allowed to have multiple applications, each in a different language, utilize the same back end database. With ACCESS, it is difficult to separate the database from the associated app. As a DBA, this makes my skin crawl :) For this development model, mySQL is so much cleaner.

dr_evil
dr_evil

Yes your right access isnt that when it comes to publishing on the web so theres its drawback. I still like it though but there again I'm stuck in my ways lol

dr_evil
dr_evil

Your right about mysql and others problem is that cost isnt the issue its what gets the job done productively and quickly. problem is that microsoft cornered the market it is was the done thing to learn these applications and because of this the knowledge base is still strong in these area's. Its hard to ask amny companies to suddenly switch to the alternatives and with that comes major cost and training as well as other issue's. Then theres development issue's yes there are forums and online help to but the technical assitance to business is a primary factor and it always reflects the cost of these softwares. So yes I can see the use for the other softwares but when we enter the realms of the business market this is a whole new ball park and executive decisions lie at this point. Like I said in a reply to this discussion its down to choice really and what your easy with and yes your right they are good alternatives.

angeleyes
angeleyes

In business it comes down to cost, and not just the cost of the software. There are the costs of migration, then training (including the trainer, the class space for the trainer, the time of the employees who will need to be clocked in, the loss in time those employees would normaly be spending performing their regular tasks if they are being trained during their normal shift, training for indivuduals that missed the first session(s), etc...), the hardware (if you need to upgrade the server hardware or server software for some reason or plain just purchase a server), the software that will connect to the database or paying for someone to .Dev it, by the time your done your looking at a possible cost overhead in the thousands for your free software. No matter what you like or dislike, I agree that the tools should fit the job. You wouldn't try to build a house with just screw driver after all, and if you are... find another day job the current one won't be there for long. So match what will match the needs and costs of the project, not the whims of an opinion. Access is by far and large one of the most easy application databases to use, unless you have been in IT or keep up with IT ins ome way most people are going to ask what this access program that came bundled with MS do the research and figure it might work for thier database needs. For home to small and maybe even medium sized (departmentalized) business use access is going to fill that need, wont' cost a penny software wize as most every company in the world is using a MS Office that comes prebundled with it, no download or setup necessary just the .Dev part. Access is also a great way to prototype a database prior to putting it on a different engine such as mySQL or SQL. I have dinner to go eat or I would keep going, let me just leave my comments here... Just because it works, doesn't mean you should use it and deffinatly doesn't mean it will be cost effect. Just because In Your Humble Opinion it's the best doesn't mean something else will get the job done better and be more cost effective. There are hundreds of solutions out there for a reason, and it's not just because one is better than the other.

dr_evil
dr_evil

I think getting it down to basics that one database is as good as another given the job its intended for. I actualy like Access more than the others but there again I was around when there was very little alternatives out there to match its power and flexibillity. I find it easy to learn and productively its a safe bet. The point we are at now though mean we have a large choice and there are other applications designed to meet the greater demands of todays business needs. what its come down to know is preference and nothing short of it. the office applications that microsoft created where not just for the business market they had the insight to flex its muscle into home market so it had to be flexable in needs for all sectors. so Access along with its rivals are going to have dislikes and likes because of this flexabillity. But ending on this Access for me is my personal choice because it extends to all people at all levels and if I was learning to use database then I would allways recommend it.

Snak
Snak

.... those links just take me to a web hosting advert. I'm aware that Access 2007 has ommissions. However we are still taking XP formatted PC's and are not moving to Vista in the near future. Nor are we installing O2K7 across the board as yet. Having said that, I do not use VBA (or indeed, any version of VB). Any and all functions within my Access databases use queries and/or macros so that if I do get run over by the proverbial bus, anyone can step in and take over.

jdowski
jdowski

Since you are a DBA I can sort of understand your point but Access has a back end, Jet. It's just that to most users you never really see the separation between Jet, the database and Access as a front end/reporting tool. You can replicate most types of stored procedures, constraints, & triggers in Access through the use of forms, macros, vba, etc. That fact that you not comfortable with the GUI centric development environment speaks only to your experience as a DBA. I, as an analyst connected to the business, would not be comfortable trying to build database structures via a command line. No right or wrong necessarily....we're just coming from different backgrounds.

dilip.talekar
dilip.talekar

I have worked a lot on Access. I found we can use it as multiuser on LAN only. for Web & commercial database like Invoicing,Inventory, FA(finance accounting) definately it is not useful. I use VB 6.0 & MS Access 2000.