Sometimes I just want to scream! Trying to use Excel as a Database

[dflak]

This is not to pick on anyone in particular; it's a wide-spread issue. In a post (I won't mention which one so as to protect the innocent - besides it could any of a dozen posts that happen here in a week), the poster wants to add a "record" to a master file and have it automatically put the record in a subordinate tab. This isn't so bad. There's probably two or three posts a day asking for this.

However, in this post, when the person in the record changes departments, the poster wants to find the record in it's subordinate tab, remove it and put it in the new tab. Yes, this can be done with some VB code. It's totally automatic in a database.

Then there are folks who want to "share" a workbook and have multiple people work on it at the same time. You can do it, unless the workbook has macros. So either you put the workbook out there so only one person at a time can work on it, or you come up with some scheme where the workbook downloads a data file, the user works on it and then writes back to the file: last one to save to the data file wins and other changes are lost. Of course, you can write code to compare the differences and change only those rows that were changed. This code has already been written, it's called Access, SQL Server, etc.

I realize that this is a case of "If all you have is a hammer, then all your problems will look like nails."

I know part of this is economics. Excel comes as part of many packages such as the Student (Military) Edition whereas Outlook and Access are left out of these packages. So at a personal level, this could be an issue - although 5 licenses for the complete package under Office 365 for $99 a year is kind of hard to beat especially if you have multiple computers, tablets and other devices. I don't know if these licensing conditions and costs are the same outside the U. S.

Companies should be able to afford at lest ONE Access license. No additional licenses should be required. Excel's MS-Query can do most of the reporting and if you have to do data entry, a free, runtime only license will work for you (and probably even better since you can't change tables and queries and such with a runtime-only license; you do not have to protect the end user from themselves.)

So, I'm blaming it mostly on lack of experience. People know and understand Excel. Databases are new to them. Many companies don't have an understanding of them either. People are afraid of them. So they try to use a tool that is unfit for the job. The company only needs one person (plus backup) who understands databases. The rest can make do with front end databases for data entry and canned reports, or MS-Query for reports that require flexibility.

Excel has a lot of database-like features and is adding more with each release. However, it falls well short of being able to do complete database functions without a helping hand. It's ok for flat file, single-table, operations. However, as soon as you link to another table in a relationship diagram, troubles will begin.

So how do you tell a poster, tactfully, that they should be using a database?

[TMS]

People know and understand Excel.

Sadly, probably not as well as they think they do.

So how do you tell a poster, tactfully, that they should be using a database?

Probably quickly, quietly, and step away ... NOW.

I think you have highlighted the issues with Access: cost, understanding, and learning curve. For a small company, I suspect that the understanding and learning curve will be the biggest issues, unless they already have someone in-house who can design the database and interface.

But, totally agree with you in relation to trying to get Excel to do something it is just not capable of.

I did do something a while back for someone that involved an Excel "back-end database" and front end macro enabled workbooks that would open the database to load reference data and then, when required, open the database to add records, etc. That involved checking if the database had been opened read-only or not, looping if necessary, and updating and closing the database as quickly as possible. The company didn't release the "solution" they were developing so it never got tested in the real world and, in many senses, I was quite relieved. I'm sure the design would have coped with a small number of transactions from a small number of users but, as demands grew, I'm not convinced.

[FDibbins]

Im sure we all see this on many different levels and on different topics (can excel make my coffee while i go play golf).

If explaining the difficulties does not work, or if you really feel the request is that outlandish, then maybe just step away, or dont even approach it in the 1st place lol

[snb]

I don't see the problem.
Dependent of your proficiency in VBA you can very easily build a database in Excel or Word. But you have to abandon the Access database concept first and will have to analyse and redesign the tasks of those who will use your application.

[dflak]

One of the issues that Access has is its very narrow niche. It's good for a "mom and pop" enterprise and for administrative tasks in larger companies. If you are running a bigger business, then you need to move up to something more powerful and a lot more expensive. But I would still rather run a business on Access than trying to run it on Excel.

The best of both worlds is the combination of the two: use a database to manage the data, and Excel to report it. I work with some people who have excellent SQL skills and write hundreds of lines of code to produce a one-look view equivalent of an Excel pivot table. I can change the view of the same data with a pivot table in a matter of seconds merely by dragging and dropping. For them it's a two hour job.

Which brings me to my other pet peeve: a lot of the spreadsheets we see here are obviously reports produced by databases and formatting with data appearing in various columns and merged cells so as to look pretty to the human reader. Then we are asked to convert this back into usable data.

Sometimes the person has no choice; the report comes from sources unknown or a third party over which there is no control. Most of the time, it's political. "I'll give you the report, but I won't let you touch my database even with read only access." In that case, ask for a dump of the data in a format you can use. But even then there is probably a bureaucratic mountain that has to be moved to get a new report.

I've worked with a company where I built a warehouse management system in Access because the IT department deemed the project "too small" to be worthy of their attention. The managers of this project came to me and asked if I could develop reporting for them based on the way they kept records in Excel. They were doing things like putting three pieces of data in one cell and combining comments with numerical data. It looked nice as a status report for management but was about as useful as a PowerPoint presentation for managing data.

So yeah, ignorance, cost and learning curve all mitigate against databases, but so do corporate politics.

[Kyle123]

As a rule of thumb, I don't respond to threads that refer to their workbook as a database. Though I'll occasionally step in to ask the user if they have considered what they are actually asking for - a stock management solution appears commonly and this would be much better serviced by simply buying one, they can be cheap and will have solved problems that the op hasn't even considered yet.

I'm sure I used to have a canned reply for why excel wasn't a good idea for a database, but it seems to have disappeared

[xladept]

When the "Temp" industry was flourishing, I got certified in Access. And, testing at the agencies, maxed both the Access and the Excel tests.

But, I got Assignments in Excel, Word and Powerpoint - never an Access gig. And now, after all these years, the gears have rusted.

When processing is to be done Excel is needed and with a million rows why would Mom and Pop bother with Access which is so inaccessible?

[Kyle123]

When processing is to be done Excel is needed and with a million rows why would Mom and Pop bother with Access which is so inaccessible?

They wouldn't, Access isn't for processing, it's for storage. Processing would normally done in Excel.

[TMS]

... and with a million rows why would Mom and Pop bother with Access ...

Being realistic, if you actually have a million rows of data in Excel, it will probably grind to a halt, even if you don't do anything to it.

[xladept]

I don't do realistic

[dflak]

I do have spreadsheets with hundreds of thousands of rows of data ... imported from a database.

[xladept]

The biggest I've worked on was just under 200000 rows and about 170 columns.