MS Access vs Excel

Post date: Jan 29, 2013 3:43:44 PM

I often see forum posts asking when to use MS Access versus Excel. I'd prefer people ask when to use a DBMS (Database Management System) versus Excel.

Excel is not marketed as DBMS (Database Management System). XL supports some DBMS features. XL also supports some word processing features. But those who know Word don't use XL to write memos and, in general, those who know a DBMS don't use Excel for data.

There are times when Excel is appropriate to store small lists. Here are criteria I use to determine what goes in Excel and what goes in a DBMS. Store data in Excel when data:

    1. Is maintained by 1 person only

    2. Is no more than a few thousand records

    3. Does not need to integrate with any non-office applications

    4. Does not need to be shared across a wide area network

    5. Sharing performance is not critical

    6. Does not have more than a very few relationships with other data

If any of the above are not true, consider a product marketed as a DBMS. The first DBMS many Excel users look to is MS Access. Many people love MS Access. I use it when appropriate. But just be aware that from a DBMS POV it is the least capable of just about all other DBMS - some of which are free. MS Access':

    • maximum record length is shorter

    • maximum database size is smaller

    • maximum concurrent users is fewer

    • performance is slower

    • adherence to ANSI SQL standards is poorer

    • cost is higher

    • and the earnings potential of MS Access developers is less

...than MySQL or SQL Server Express.

This is why I prefer the question be broader than just MS Access. Excel works with all DBMSs. And that is the key IMHO. Excel works WITH DBMSs. I use Excel’s:

    • MS Query, PowerPivot, PowerQuery, or VBA and ADO to pull data from DBMSs

    • Rich function library to "enrich" extracted data with calculated columns.

    • PivotTables to summarize data and provide drilldown

    • Charts to visualize data

    • VBA to enhance and automate any and all of the above

Excel WITH databases is a winning combination.

After note: RDBMS means "Relational Database Management System". For a DBMS to also be an RDBMS, records must be addressable by key value. Excel can do that. While this means Excel meets the RDBMS criteria, Excel lacks "Views" and "Indexes" found in every product marketed as an RDBMS. Views and Indexes make finding and/or joining records of different tables by key value quick. Excel relies on functions created by users to join records. While Excel's functions are quick within the active workbook and over a small dataset, they cannot be used to access records via SQL which limits SQL queries to what is known as a "full table scan". Full table scans are the slowest search method and functionally no different than searching non-RDBMS systems.