Tuesday, February 1, 2011

Top 10 Reasons to use MS Access

Microsoft Access is the most popular database program in the world for a number of reasons. One of the biggest is that non-IT professionals can use it to develop solutions that are cost-effective and that address a wide range of data management issues. Access essentially brings Visual Basic, SQL Server, and Crystal reports all down into one nice package that almost anyone can start using.

Here are the Top 10 reasons why I like MS Access....

10. Great for projects with little or no budget

Solutions can be built quickly and, if done in house, for almost no dollar cost. Many small businesses already own a version of Microsoft Access and just aren't using it. Either in house our through a consultant, applications can be built quickly and at low cost that solve many issues and have an immediate effect on your business.

9. Allows for Rapid Application Development

Building in Access is a great Agile development tool. A non-IT professional can quickly start building an application and will quickly see how Access can address their data challenges. Access is also a great environment for prototyping.

Many databases built in Access solve a specific problem and may be short term solutions. They last for a while and then disappear as the issue is resolved.

Other databases start out to address one issue but evolve to address many other needs. These may eventually outgrow Access but, when its time to upscale from your Access database to a SQL server database, you are well prepared because you have your Access database to serve as a prototype.

8. Easily Integrates with Microsoft Office

One of the best features of working with Microsoft Access is its ability to integrate with other MS Office products. You can get the most out of Office when you let each product do what it was intended to do; MS Word was created to do Word Processing, Excel, for number crunching and graphs, and Powerpoint for presentations. Data can be easily moved between Excel and Access, letting you use the best features of both. Access queries can easily provide a data source for a Word Mail Merge document. The integration of all the Microsoft Office components creates an environment that exceeds the sum of the parts.

7. Great for Data Entry

Through the use of Forms, Access allows for very customized and easy to use forms that are great for data entry. The combination of these allow for the creation of a very user friendly interface to interact with your database. Business rules can easily be built in and through the use of combo boxes and list boxes, data entry can be simplified and uniform.

6. Interface with other Database formats
Access should be described as a tool more than just a program. You can build a solution in Access and yet link to data in other applications such as dBase, Paradox, FoxPro, as well as SQL Server, Oracle, and DB2 through ODBC connections.

You can even link to other Access databases. This ability basically solves any space limitations you may worry about. Tables can be stored in as many databases as you need; each allowing for up to 2 gig of data. Then the tables are linked back together in your front end application. Basically, the only limitation to the size of your database will be the size of your hard drive or file server.

5. Query Designer

The query design tool in Access is excellent. Dragging and dropping fields into your query couldn't be easier. It is laid out nicely and allows even the most novice query writer to begin building useful queries. As you get more experienced, you can easily switch to the SQL view to write more complex queries.

Even experienced SQL writers will prefer to use the query designer just for the ease of it...I know I do.

4. Report Generator

Now that you were able to get your data organized and in one place, you'll want reports. Bigger database systems like Oracle and SQL require a separate system to run reports. Access brings a reporting tool right into the same package. Reports can be built directly against tables or on top of pre-existing queries. Experienced and inexperienced users, through the help of wizards, can build very fancy reports quickly and easily. There are even some preformatted reports to help with labels.

3. No need for a server or extra hardware

Unlike, Oracle or SQL, MS Access does not require the use of a server. The MS Access program and Jet Database Engine run on each machine. This means, instead of a server doing the work, each machine using the database will handle the processing.

Very often, we will build our application to utilize an existing file server. The data will be stored in an Access Database on the file server and each user will have their own version of the program (the Forms, Queries, Reports, etc) on their own desktops. If the company doesn't have an existing file server, we often just dedicate one of the workstations as the server and let the datatables reside there. Another possible solution is investing in a Network Attached Storage device. These devices are small and cheep and can take the place of a file server nicely.

2. Multiple users

Although Access has advantages for the single user, sharing the the database with multiple people all at the same time is where you will really see the biggest impact. MS Access will handle all the record locking and worrying about who made what changes to which records. Unlike working with Excel, you'll never get that annoying message that someone already has your spreadsheet open. Access was designed to be used by many users (by default 255 can use it at one time). Through the use of forms and some coding, security can be set up so that certain users can only view certain data. Of course you can get very complex with security as well and get into all sorts of levels with Read/View only or Read/Write levels.

1. Programming with Macro's and VBA

One of my favorite aspects of Access is that it allows you to program using Macro's or VBA code. When I first started using Access (back in Access 2.0), Macro's gave me my first taste of programming. While building Forms, I discovered how to make buttons work through the use of macro's. I could program buttons to open new Forms, run reports, run queries, etc. They are easy to use and give users a good taste of how the database can be transformed into an application.

As if Macro's weren't enough, Access also gives you the option to program in VBA. Once you become more experienced you will realize how powerful VBA can be and how many more options it gives you. VBA is a version of Visual Basic. It is the code that controls the application and allows all the pieces of your database to work together as one, seamless, application.

These are just a few of the reasons I like using Access. While looking back at these, I can’t help but realize a recurring theme in most of them. Access provides all the tools necessary for a very inexperienced user to create a new database and start building while at the same time providing experienced users a great environment to build more complex systems.

How great is a program that really allows you to grow your skills all within the same application?