So who is going to click “External Data”-> “New Data Source” -> “From File” -> “Excel”?:eek:
My anecdote: In my first postdoc lab job, I was put in charge of the “databases”. Everyone had their projects in separate Excel spreadsheets. One of the postdocs who had been in the lab a while was showing me the lab’s files and folders on the server.
“And here are all the databases.”
I said “Where? I don’t see any database icons.”
I get looked at like I’m stupid.
“All these.” the guy says pointing at the Excel file icons.
I said “Those are spreadsheets. Do we have Microsoft Access?”
Again I get looked at like I’m stupid.
“Of course we have access to Microsoft. How do you think we make the databases?”
I’ve found this to be surprisingly common in large companies (and small companies, and medium-sized companies).
To answer the OP, I think Troutman nailed it:
Access doesn’t scale particularly well, it’s probably not backed up consistently in most cases, etc. I’ve dealt with a lot of projects that started out as this little Access DB that a single user or small team needed that have grown the point that it’s considered business-critical, but it has issues galore and they need to migrate it to a proper database system.
I hope their interfase localization is better than the translation of their webpage, a lot of it seems to have been translated by the “hit a dictionary with a brick” method.
My crystal ball says he’s taken a look at your data, seen that a lot of pieces of it are repeated (for example, the same vendor appears multiple times) and wants to separate those bits into different tables. You’ll choose the vendor for a given item, its associated data (address, phone, etc.) gets read from the table. You need to update a vendor’s data, you just update it in the “vendors” table. Can that be done in excel? Yeah. But doing it in Access needs even less work than doing it in Excel.
I totally agree. Which is why using Access for a lot of purposes is like using a band saw to cut peanut butter sandwiches in half. Way more power than you need, when a simple hand tool works and doesn’t need tremendous care and upkeep.
Without knowing how complicated the task is, we really don’t know whether Excel, Access or a real database is most suitable.
Excel is still a thing
That little misunderstanding is hilarious.
For my industry, there’s nothing between Excel and a full, proper database system. I’ve never seen nor worked with Access.
Looking through the Wikipedia page, though, I’d say that it probably would serve as a pretty decent intermediary.
While Excel can be used to create forms, reports, etc. it’s not really structured to make that anything but a spaghetti of nonsense to reverse engineer if you want to change anything, and I suspect that even the original creator couldn’t modify the thing a few months after figuring out how to weave everything together as he had originally wanted.
On the other hand, a standard database doesn’t have any powers to display a form, report, nor anything else. It just holds data. And while it has access mechanisms that allow one to create reports, you have to set up other applications and tools that call into it, in order for that to happen. A database, all on its lonesome, is sort of like an elaborate paperweight. Minus a programmer and time, you’re not going to get much out of it.
But, the one nice thing about a standard database is that it’s centralized and allows people to have access to a shared, consistent data set. They don’t each have their own copy - where each person has a slightly different set of things available, at slightly different levels of recency. Instead, they have all of the data (which they’ve been granted permission to access) and it’s all up-to-date. The applications written on top of the database will be written in a source code-based programming language that any other developer can read, understand, and modify after the fact. All of those features are available to everyone and have been created by a proper application developer and vetted by a quality assurance person.
The downside to a shared dataset is that it’s inherently unsafe.
If someone deletes their own copy of some Excel document, no big worry. They can just get someone to email it to them again. Whereas if you clear the one, giant, shared database table of all the business’ shared knowledge…well…that’s going to be a problem. You need to set up limits to what people can do, set up systems to cut backups, etc. so that the central-point-of-failure problem isn’t a problem. The reason that you get a programmer to get your database running is because you need to build UI that has a bunch of security and input checks in it, so that the database isn’t borked by a single user, only allowing them access through a tightly defined, narrow inlet. Building that is costly and difficult to do correctly, though.
Access looks like it tries to fit a middle ground to these two extremes. But, it’s a pretty big middle and it looks like Access doesn’t offer a one-size-fits-all solution. You have to pick which end of the spectrum makes the most sense for your people. You can set it up to be not much different from Excel or not much different from a standard database.
In terms of growth, if you expect this data to actually continue to sit around and be used, Access is probably better than Excel. But you would need to stop people from using Excel and force them to use the new stuff or else it will all have been a waste.
What I would probably worry about, though, is that the new guy has no real idea what he’s doing, doesn’t understand the ramifications of the settings that he chooses, or doesn’t have a good view of what setup would make the most sense for everyone there. I would probably want to have a second person, whom I trust and who understands the business, learn Access and work with the guy, to make sure that the setup made sense for everyone and also just to have two people who know the stuff available after the fact. First step for that person would simply be to make sure that it was worth doing to begin with.
I’m a big fan of Access.
I do the program for a science fiction convention. I could not do it without Access. I use queries to get everyone’s program schedule, so I can print it out. Make a change? Just bring up the query (or form) and press “Print.” Have I scheduled someone twice at the same time? Look at the query (which also shows if I’ve overscheduled someone).
I also use it to keep track of my short story submissions, which lets me see all sorts of things I need to know with a click or two.
I used it at work to massage data that come out of our Banner system so it could be uploaded into our course evaluations.
It’s really a great program. I couldn’t do a lot of things if I didn’t have it.
Gah, and I thought the government was bad. We only had a vitally important application turn out to be an Access Database saved on the shared drive :smack:
You don’t need any coding to use Access. It does more, with less coding, than Excel. Back in the day, that was one of the complaints about Access: that people who knew no coding (and had no discipline, and had no work place sanitation)) could knock out business critical applications.
It does take some learning: it’s like going from using a piece of paper, to using a filing cabinet. But it’s not true to call that “coding”. To start doing useful tasks in Access, you have to learn what button/menu to click.
The big difference from Excel is that Access really does want you to / help you to, separate data entry from data display/reporting. That’s a big step up. Of course it has corresponding benefits.
Access is, in a sense, obsolete. Many of the tasks we used to do are now done by off-the-shelf software. Much of the software written now is phone apps. Competing products have improved dramatically, both in the GUI and on the database side: Access is no longer night-and-day better for as many situations as it used to be.
“Scale” is the key word.
If it’s a one-person tool (or a department of about, say , 5 people who will only use the tool one at a time) and the record count is low, and there’s not a lot of cross-linking of tables (i.e. look up state from area code or zip code, et descriptions of shipping codes, match employee list with dependant list, supplier with order, that sort of thing) then Excel is your quick and simple tool.
If the database gets complex, if you want to consolidate data to lookup tables (“Normalization”) then creating a database in Access instead of multiple Excel sheets is much better. This prevents typos - “No wonder we missed listing that entry, it was entered as StriaghtDope” and requires users to select from specific codes to ensure data consistency. It also allows for concurrent use.
For serious databases, which are mission critical and contain massive amounts of data, an actual database (SQL Server or similar) is best.
The front end can be anything, but MS Access or MS Excel both allow for Visual Basic (VBA) programs to be created quickly and easily to access (!) the data. As you move up the above scale, there are several requirements - more in-depth documentation is important due to increasing complexity.
SQL Server does not require a dedicated server, but as the system grows it’s harder to get by without one, particularly for a company-wide application. SQL Server management requires its own level of knowledge to manage, so you will need a SQL database manager.
Access is still a flat-file database system with indexes, so you are still relegated to reading large quantities of data - particularly bad (even back in the old dBase days I saw this) when reading the data from a common file server.
Excel is even worse - you have to read the entire “database” file when you open it. If you have a maze of multiple linked spreadsheets it’s even worse.
Then from a security standpoint - Excel is wide open - it’s difficult to do a data entry spreadsheet and protect it at the same time, and any tricks to do so add to its complexity. Security works better on Access and best on SQL, but at the cost of maintaining users and permissions.
So the short answer is - it’s a sliding scale. How important is it? How big is it, how many users? How much resources do you want to spend on setting up and maintain it? How much security do you need?
Each is a tool for a specific target range of applications.
What I was trying to point out is that if an inexperienced user walks up to a database expert and says the word “database”, they may have very different ideas/definitions of the product in their minds.
My previous experience with this is that we had a “database” in Excel. Everyone (like 4 people) knew it, every understood it. IT WORKED.
In comes a new coworker that takes a look at it and says “this should be in Access!!” He then proceeds to get this whole thing setup in Access and tells us all these great things we can do in access. We didn’t need any of those things. We tried to use Access for a while but it eventually failed. We went back to Excel and everyone was happy.
So I am just a bit skeptical when people poo-poo the use of Excel to store information. If it meets the needs of the company, and other products don’t provide benefits, then don’t deride them for being so uneducated that they think Excel is a way to handle a data.
Database guy checking in.
md2000 has some great points, and so do several others who talk about the actual quality of your data. I completely understand the attitude of folks on the proverbial “shop floor” who don’t want to convert to a more robust database system, but if data consistency (ie everyone sees the same thing) and reliability (we all saw the same thing last wednesday, and if we restore the DB, everyone will still see the same thing – as opposed to “Jim’s computer crashed while he was updating the copy on the file server, so it’s corrupt now and we have to revert back to Paul’s copy, which is 2 days old, but nobody knew that Linda had a 1-day old copy that everyone in her department is still using…”) then a full-fledged DB is the right tool for the job, especially if it’s critical data that could make or break the business.
There are very few use cases for Access as anything other than a front end (ie something that links to a full-fledged DB but itself only contains the code to make pretty forms and reports), and none that I can think of when critical data must be available to multiple people.
That said, it is NOT a trivial effort to pivot to a full-fledged database system, and I understand the frustration with it. I wouldn’t recommend switching to Access in this instance, as it’s likely vulnerable to most of the same problems as Excel. Either go big with a full DB (something like Postgres can be done for free if you don’t want to pay for SQL Server) or just stick with what’s working.
Nope. He dumped the entire Excel into Access and found 12 duplicates that turned out to not be duplicates because of different entries in a column he had not looked in. I found this easily in Excel using conditional formatting that immediately showed me they were not actually duplicates at all. It’s just “I like this better, even though no one else in this group knows it and the company does not support it nor offer training”. Exact same thing that happened in 2011 - one guy establishes the Access let’s-call-it-a-file, cannot be bothered to train anyone else in the group to maintain it, leaves the group and the entire database must be migrated -* manually* - back into the original Excel file that everybody was fine with to begin with. Same situation, including several weeks of money being spent to migrate a working system into a different one with the definite possibility of another $$$ being spent to migrate it back if he leaves. I’m in that “zero f*cks to give” stage of preretirement, so getting me to do this is not a thing I’m willing to try. Bad attitude? Sure, but I’ve waited a long time to get to this stage and done a lot of things I really didn’t want to, so this time I’m just going to nope out.
Why spoil an otherwise good exposition with factual errors?
Access is not a ‘flat file’ database system. And since, at the operating system level, Access databases are represented by records, use of it does not, in general, require reading (or writing) chunks of data any larger than the file-system cluster sixe – which is the same for any other Windows database system, including SQL server.
We could have a discussion about the caching behavior of the Access client and server (the server is the “server” service on the file server), about the (client-server) network protocols, about the effect of protocol encryption and record locking, about the history and use of the database primitives provided by the OS and the dead end that has lead us too, about the reasons for and the effect of the Access authentication system, and about the comparison to the way Excel is handled… but it’s pretty much irrelevant to the question under discussion,
—except to say that Access handles multi-user updates better than Excel does.
Most relational databases do this and Access isn’t really different - they store the data in some small number of data files while providing a logical layer for that data (e.g. tables, columns, etc.) as well as indexes for faster access (sometimes).
I wish Access would do what I tell it to, instead of thinking it knows what I want better than I do and doing what it wants. :mad:
OK, so he’s just a bad user. My condolences.
Even if there were duplicates and depending on the situation it could have been overengineering. I’ve seen a lot more overusing of Word, PowerPoint or Excel than of Access, but I think that’s mainly because a lot more people use Word, PowerPoint or Excel than Access: bad users exist everywhere. My biggest beef with Access is precisely that Microsoft insists in telling me that I should not want to use it and should be delighted to use other programs that match neither my needs nor my wants.
If you only have that problem with Access, Access is the only Microsoft product you use. It might even be the only computer program you use… wait, you posted that from a browser, didn’t you? So it can’t be the only program you use…
I can’t speak to OP’s specific problem, but in my experience teams using an underpowered tool for a problem that has outgrown its use case is a much more common situation than teams using an overpowered tool when the problem would be handled just fine by a simpler one (which I’ve still seen, but less often). And Microsoft Excel is the most common “underpowered tool” in this scenario.
This is common sentiment.