Access vs Excel

[jyl]

I'm proficient in Excel, have basically lived in it for 20+ years.

As a result, whenever I have a task, I try to do it in Excel. Hammer-nail, etc.

I'm wondering if I should learn Access for this next task.

I'd like to build a lightweight customer relations database that will do things like hold key data about clients and their accounts, save email chains, track ongoing tasks and opportunities, remind me of key dates, hold my client-specific notes, and so on.

Yes I could simply use a cloud CRM but I'd like to try rolling my own. I've used Salesforce and found its interface awful and designed for a workflow that is too different from mine.

So, any Access experts here?

[Shaun @ Tru6]

I once was a long time ago, it's a great foundation to building any kind of data-focused custom application quickly and easily. I was using it 20 years and learned visual basic and SQL to create applications for Solomon Smith Barney and a few large actuarial firms. I'm sure it's even easier today to create and customize a template to do exactly what you want, how you want. Creating step-wise comboboxes populated by SQL is terribly addictive.

[RKDinOKC]

I use Excel for spreadsheets, and Filemaker for databases.

[MysticLlama]

I only allow use of Access for one time projects. Combine multiple tables, query, reconcile or clean up the data, then shut it down.

I've seen far too many live for years as the lynch pin of an org that was all sorts of a pain to connect to in a consistent fashion.

So this is about how I feel: https://www.microsoft.com/en-us/microsoft-365/blog/2012/08/22/feral-cats-managing-access-databases-in-your-organization/

For a CRM/Ops system, I'm using Dynamics CRM. It can be made kinda like super Access and tailored however. Takes some time, but you can do anything you want to the forms, workflows, etc. Salesforce allows all of the same customization, but since we were already 100% Office 365 and various things linked into that, keeping it all integrated made sense.

So it depends. If you're doing something small for just you or you and a couple people, you could get away with Access I suppose. But if you ever want it to be more than that, you're starting in a bit of a hole.

[RWebb]

Excel is like a flat file database - if you need relational then go to Access

I haven't used it in decades, and was never able to wean myself off of dBase II, etc. - ultimately my need for a database died...

[beatnavy]

I've used the crap out of Access for about 20 years. I love it, but I also recognize it's limitations -- it's primarily for building small workgroup applications (e.g., 5 concurrent users or less). Beyond that you're taxing it (unless you build the front-end in Access and attach it to something like SQL Server on the backend). It's also not the most secure platform -- something to keep in mind if your data is sensitive. Depending on relationships and how the db is built, Access can handle in the 10's of thousands to 100's of thousands of records. Beyond that you really need something more robust. Otherwise, it's great for quickly solving business / data management solutions.

I've built a couple of CRM systems for customers over the years -- the relational nature of data is well suited for something like Access (or something more robust).

Access does have a learning curve. Some people never quite get it, but if you're willing to learn, it's a pretty cool tool and really useful as a tool for small groups (or individuals). It REALLY helps to learn macros and VB. With some experience you can build some pretty slick applications using forms and coding.

Are you pretty good at picking up skills like that?

[id10t]

Originally Posted by RWebb

Excel is like a flat file database - if you need relational then go to Access

I haven't used it in decades, and was never able to wean myself off of dBase II, etc. - ultimately my need for a database died...

If you need relational then I'd recommend MariaDB (the Free fork of MySQL. You can still get MySQL free and kinda Free but it isn't as Free as some would like, hence the fork) and any programming language, perhaps a browser front end.

Learning how to do it using MySQL and say Java or Python or PHP won't be any harder than doing it with Access, and it frees you up on a lot of things, like being able to move to other bigger better platforms/servers, change operating systems, you aren't dependent on future compatibility with Office stuff, etc.

If you really want access the OpenOffice equivalent (Base) does the form design, etc. like Access but talks to MySQL/MariaDB on the back end.

If you have an Access db, it is possible to do a one time one way "upgrade" to move the data to a MS SQL server but then you are still stuck with MS and license fees and depending on their future compatibility.

[RKDinOKC]

Filemaker is easier to learn than Access and SQL and scales up well.

Tried to move a fairy simple relation to Access and there were a few simple things Access just couldn't do.

[Bill Douglas]

Excel, that's a bit modern. Lotus 123.

The great thing about all of them is they copy each other so the differences are pretty subtle.