Paul Strauss

Microsoft Access is a Relational Database Management System. In less technical terms, this means that MS Access is a complete environment for creating Information Management computer programs for almost any purpose. The term "Relational" that is so often associated with databases refers to the mathematical foundations upon which MS Access is built. The significance of being a relational database is the reliability that derives from this mathematical foundation. Being a relational database helps to guarantee that when a data retrieval operation is performed, based on some criteria, all of the information retrieved satisfies the criteria and no information that doesn't meet the criteria is retrieved; that is, you are confident that what you asked for it exactly what the database returns. Other non-relational database management systems can't make this claim with as much certainty.

There are quite a few good reasons to use MS Access for your application. Some of these reasons many not apply to your current situation, but situations have a way of changing.

• The report-writer in MS Access is one of the best available. There isn't much it can't do, and in general, I can create a report, from start to finish, in about two to four hours. Obviously, more complex reports take longer to write, but my experience is that most reports don't require very much effort.
• A major function of a database management program is to insure that the data input into the database is valid and correct. This means that, for example, telephone numbers look like valid telephone numbers, both with and without the area code. There is not much that can be done to insure that the telephone number entered is actually a real telephone number, but at least I can insure that it could be. For Zip Codes, not only can I insure that it looks like a valid 5- or 9-digit Zip Code, but I can actually verify that the Zip Code exists and corresponds to the other address information. All of this, and more, can be done with plug-in modules that either I developed and own, or which can be purchased from third-party providers and a very reasonable cost.
• Another aspect of insuring valid data is making sure that there is no data duplication. For example, you don't want to be able to enter the same employee into the database more than once. If this happens, then it is common to loose some of that employees information. If the information that is lost is part of the employee's salary records, no one will be happy with the situation. In a properly executed database, MS Access can insure that this doesn't happen and can do much of this work without having to write any code to do it.
• As part of the MS Office Suite, MS Access integrates seamlessly with MS Word and Excel. While the report-writing capabilities of MS Access are superb, a report is not always what you want. It is very easy to set-up a MS Word Mail Merge document and have it retrieve its data directly from your MS Access database. You can even select the data that you want Mail Merged from a simple, user-friendly, MS Access data-entry form.
• The development environment that MS Access provides supports rapid application development. This means that it will cost you less to have your application written in MS Access rather than in, say, Visual Basic. MS Access is a data management tool and everything about it is optimized for that purpose. Of course, not everything is a data management task, and in that case, other options should be explored. My experience is such that I can suggest other languages that would be more appropriate to your requirements and provide you a non-Access solution in that language when warranted.
• MS Access can be used by non-programmers to get useful work done. The amount of training that would be required for your staff to use MS Access is rather small. If this is what you want to do, then, in addition to the database files that I provide, I will also provide a separate file, called a sandbox, that your staff can use to create their own queries, screens, and reports.

There are a few misconceptions about MS Access that I would like to dispel. In my experience, many people who lambaste MS Access are not very well-aquainted with the product or are biased. Although I appreciate MS Access and all of its strengths and significant features, I realize that there are instances where it is not the best solution for a situation and I will not hesitate to say so. I try to remain objective in my recommendations, always keeping the best interests of my clients as a primary concern.

• When MS Access is disparaged, the complaint often concerns the limitations of the Jet Engine that comes with MS Access. The Jet Engine is the part of MS Access that handles all of its data storage and retrieval functions. It is just one of several database storage engines that are available. It is no secret that the Jet Engine has some limitations; Microsoft implicitly admits this. However, there is an easy way to overcome those limitations – replace the Jet Engine with a more robust storage engine. Microsoft would like you to choose their SQL Server product, but it is just about as easy to use Oracle, DB2, or my favorite, MySQL.
• For many situations, the Jet Engine is quite sufficient. If the application will only be used in a small office, say up to about 10 to 15 users at a time, the Jet Engine is probably a good choice. I have seen it support up to 60 users, but I would not recommend using the Jet Engine for so many users. In this situation, I would suggest using MySQL. MySQL competes well with the database engines from Microsoft, Oracle and IBM, so you are not giving up any efficiency, reliability, or stability. In addition, it is much easier to install, configure, and maintain. And although purchase price is usually not the sole determining factor, MySQL costs only a few hundred dollars rather than a few thousand.
• MS Access can be very inexpensive because it is not necessary to purchase a licensed copy for each computer that needs to run the application. Microsoft offers a run-time license which allows the owner of the license to install the application on as many computers as desired, without any limit on the number of computers. The break-even point is around six computers.
• MS Access is not a toy or inappropriate for large, mission-critical situations as many of its detractors claim. The number of simultaneous users is limited mostly by the speed and power of the computer where your data is stored, that is, the computer on which the storage engine runs, and the speed of your network. If the number of users is large, or the amount of data is huge, then it is easy to replace the Jet Engine with a more powerful, capable, and robust storage engine, such as MySQL. The report writing capabilities of MS Access are very good, and the ability to control and validate data entry means that it can be insured that the data entered is valid and correct.

Indeed, MS Access is a most likely a good choice for your information management application.