Is Excel data model as good as or better than Microsoft Access?

Question

I'm working with several Excel worksheets and workbooks that need to consolidated or linked to each other. These workbooks will track client interactions for different team members.

I did the initial consolidation in Microsoft Excel, but realized that going forward, there's a risk of people overwriting each other or working from an older version of the master workbook.

After some research, I realized one idea could be to have each person track their own client interactions. I can regularly consolidate each workbook into the master using Excel data models.

But are there additional risk to doing this? Is an Excel data model as good as an Access database? I've shied away from Access because my team is not comfortable using SQL and overall dislike the Access Interface.

This is why I started off using Excel. But managing multiple workbooks (with changing names) has already created several version control issues. Any feedback would be helpful!

I'm limited to using Microsoft Office 365.

---

Use Cases

Log interactions

• Each person can keep a log of daily client interactions
• Individual logs are regularly synced to a master list that shows interactions for the whole team

Log contact information

• Each person can contact information for new clients
• New contact info is synced with the master list

Add or update broad company information

• Each person can add or update general company information (ex Company A is focused on xyz for 2020). This is separate from the individual interactions
• Updates to general company information can be added directly or synced with a master list

Filter for a list of interactions, contacts and broad information

• Search interactions based on criteria (ex company's city, interaction date, contact's place of employment)
• Print all information (interactions, contacts and broad information) based on a specific company name

Reporting

• Send scheduled reports (weekly, quarterly) based on recent updates and other specified criteria (ex company with the most logged interactions for the month)

Answer 1

I've shied away from Access because my team is not comfortable using SQL and overall dislike the Access Interface.

Here's your answer. There is a strong argument to use Excel because it is what your team is comfortable with, and that is pretty darned valuable.

But managing multiple workbooks (with changing names) has already created several version control issues.

You can use an Excel front end and have it connect to an Access database behind the scenes. If you store everything centrally then a lot of the versioning issues will be much easier to deal with-- for example, you can release new versions of your Excel workbook with new code, without users having to move their data. It also allows you to store data in real time, meaning that you don't have to deal with merges (and merge exceptions) any more.

Answer 2

The datamodel is (mostly) independent of the database. Excel is not a database.

Ms Access is a great RAD (rapid app devel) frontend, but db (MDAC/Jet) is limited on multiuser, security and backup (no dumps, binary log, etc), but fast and no db-server needed!

You can work with Excel, but fetch data from a db (company enviroment Sql Server, MySql, Postgre are recommended (besides other)) as for speed, function, security and backup from a data connection. Also the MDAC connectors bitness issue is sad! Connectors (ODBC/ADO) can't be used side by side for x86/x64 apps, because you can only install one bitness, limiied to apps of that bitness (this is a killer of MDAC at all)!

Basic SQL is mandatory (for devel, not user), but you will see the benefits in pure excel too, as you can use sql on it to simplify tasks (no more loops in loops on cells).

First to master is database normalization, then decide the rest. I recommend Access as you will bind data to form (updates/inserts your changes back to db) within seconds. Try that on excel