MS Access Database, Why Choose It? and Why Not Use Access Software for Your Business

Is Microsoft Access the best small business database?  What is Microsoft Access? These are the questions you may be asking yourself.

Microsoft Access is a Relational Database Management System (RDBMS), like Oracle & SQL/Server among others.   This RDBMS combines the database engine known as JET, Forms Design, Report Design, Graphics, and Visual Basic for custom program execution. Is it the right database development system for your needs?  This discussion will help you identify the pros and cons of using MS Office Access database as your software development system.We also present and discuss typical problems found in non-professionally developed databases – particularly Microsoft Access.

The following list provides answers to the question – why use Microsoft Access for consideration for your company’s database development platform:

    • Microsoft Access is available with the Microsoft Office Professional suite of business products therefore no additional database software is required if your company purchases computers with this suite of products already installed.

    • MS Access database is likely to be available and supported for years to come because Microsoft is the premier software company in the world.

    • MS Access is the most widely used desktop database system in the world.

    • If database support is important to you then Access may be your best choice since Access has more support and development consultants than any other desktop database system.

    • Access is significantly cheaper to implement and maintain compared to larger database systems such as Oracle or SQL Server.

    • Access can provide a cost benefit since consulting rates are typically lower for Access consultants compared to Oracle or SQL Server consultants.

  • Fairly complex databases can be setup and running in 1/2 the time and cost of other large database systems (the simpler the database the greater the cost advantage).

  • Microsoft Access integrates well with the other members of the Microsoft Office suite of products (Excel, Word, Outlook, etc.).

  • Other software manufacturers are more likely to provide interfaces to MS Access than any other desktop database system.

  • When designed correctly, Access databases can be ported (usually with some difficulty) to SQL Server or Oracle.  This is important if you want to start small or develop a pilot database system and then migrate to the larger database management systems.

  • A Microsoft Access system can be placed on a website for access for remote users.  Simple screens can be developed within Access, Data Access Pages. Or full control and functionality can be implemented using Active Server Page (ASP) programming.  Note that you still have the same simultaneous connection limitations described above.

 

The following are some reasons why Microsoft Access may not be your best choice for your company’s database application development.  In some cases your computer systems may not be reliable enough to handle any database management system safely.

  • The Microsoft Office suite of software products is not in your company’s approved technology set.

  • You need to capture time critical transactions – e.g., from a lab instrument

  • Any single table in your database could have more than 1 million large to medium-width records, or 5 million small-width records (i.e. 3-4 numeric or short text fields)

  • You would regularly have more than 15 people actively using the database at the same time – for example quick data entry and/or queries and reports.

  • Your database servers experience periodic, unexpected crashes – e.g. more than 1 per month (in this case you’ll have problems with any database system).

  • You require extensive database security because of the highly sensitive nature of the data. (Although you can get around this issue by using SQL Server as the database engine with an Access front-end.)

  • Your company needs to run multiple Access databases, some with Access security enabled and some without.

  • You will be sharing the database with mixed operating systems (Windows 98, Windows ME, Windows 2000, Windows XP, etc).  See an article by Microsoft on why Microsoft Access becomes corrupted.

Typical problems found with non-professional consultants development of Microsoft Access databases:

  • Poor relational design, too much duplicated data, no (appropriate) primary keys.

  • Using a Microsoft Access template database that doesn’t perfectly fit your needs (no template does!).

  • Attempt to overly control the database forms with custom controls for everything like: deleting records; moving to the next record; adding records.

  • Using Macros to handle events within the forms because the developer doesn’t know Microsoft Visual Basic for Access Applications (VB script).

  • Many ‘on format’ controls/VBA Code in reports because the developer doesn’t know the SQL programming language.

  • Consultants use of data fields to link between tables resulting in significant duplication of information and unnecessarily large tables.

  • Use of weird color combinations on forms and screen backgrounds making the information on the screen difficult to read.  See our sample Microsoft Access software forms.

All that said… there are several alternatives to Microsoft Access.  Here is a list of the main Access database alternatives: FileMaker ProFileMaker Pro is a cross-platform relational database application from FileMaker Inc. It integrates a database engine with a graphical user interface and security features, allowing users to modify the database by dragging new elements into layouts, screens, or forms. Alpha Five/Alpha AnywhereAlpha Five is a relational database management system and rapid application development platform for building desktop, web, and cross platform mobile applications. Alpha Five is no longer available because Alpha Software has migrated to a subscription based model called Alpha Anywhere that also allows for mobile computing. ParadoxParadox is a relational database management system currently published by Corel Corporation. It was originally released for DOS by Ansa Software, and then by Borland after it bought the company. A Windows version was released by Borland in 1992.Lotus ApproachLotus Approach is a relational database management system included in IBM’s Lotus SmartSuite for Microsoft Windows. As a company, Approach was founded in 1991 and has won more than 30 awards, including “best of show” at Comdex.

One key component of Microsoft Office Access is the database engine.  Also referred to as the JET (Joint Engine Technology).  This database engine is what communicates with the tables and is also used in Access Visual Basic.

If you are looking for a professional Microsoft Access programming company then contact us!

See discussions about Microsoft Office Access versus other leading database products.

For complete information about Microsoft Access databases visit Microsoft’s Access home page.

Access Database Templates:

    Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016