OP, why do you want to stand in the way of progress, particularly when you admit you aren’t going to be around to see the aftermath? I have no idea if a move to Access is the right move, but if someone else is willing to take it on, why fight it? Relying on Excel as a database is really not using a software packages to its optimum use.
If someone else wants to make the change, why fight it? Embrace it, but insist on proper documentation. If you have seniority, you should be able to insist on documentation. That is, what defines the tables? What relationships are established between the tables? And, most importantly, who is responsible for maintaining the tables and relationships?
Instead of standing in the way of progress, you should embrace it. But, use your experience to keep change for the sake of change from causing more problems than you are trying to solve.
There is a learning curve. Make sure your boss budgets for support from a developer for training.
MYSQL is almost as powerful as Oracle and very inexpensive. There’s a lot of developers available that will design the database and load in your existing data from Excel. Giving a turn key solution.
Then, you need Crystal Reports to generate the reports.
The security in MYSQL is very good. You can create user ID’s for each staff person and indicate which tables they can access.
For example, The dept head in Sales can look at his budget and purchases. He can’t see a different dept’s budget. Unless you setup security for him to do that.
It handles multiple user access really well. Several staff members can run reports from it at one time.
MS Access is limited. It’s not designed for multiple account use.
MariaDB is the newer database from the same people who originally developed MySQL; MySQL got bought by Oracle and MariaDB is still being developed. Lots of organizations big and small use it.
This thread is frightening to this GIS programmer/analyst. Excel is good for lots of things. A database it is not.
Eh, we will use Excel for the odd delimited file that needs to be imported. It does that very well. Or keep track of just what versions of GIS software has been installed on our users machines.
I understand OP, that you are retiring, from a large company. But the new guy put your data into Access??? You work for one of the largest companies in the world? What the heck do you do/produce/manage?
I’m work for small County Government. We would never consider Access. A suggestion to do so would have your credentials as any type of IT or IS person seriously questioned. No way in hell it could handle what we need. We use SQL Server.
Access would be a step up from Excel, but frying pan, meet fire.
Oh, Excel does annoying things too. Access refusing to obey what I tell it to do really slows down my work. Access 3 (or whatever it was) did believe me when I told it to export a field at a certain length. Access 10 (or whatever) says ‘Oh, you told me to export 40 characters. This one cell has 43 characters, so I’ll just shift everything in that output record to the right because humans are to stupid to know how many characters they want.’ :mad:
But yeah, the hoops you have to jump through in Excel to simply have leading zeros is a pain too. And I wish MS could understand that embedded commas are supposed to be ‘embedded’ when saving as a comma delimited file, instead of using them as delimiters.
Just very recently, I used Word to do something to a graphics file which GIMP wouldn’t let me (namely, insert a simple geometrical shape of defined size into an existing file). I know it’s not good practice, and I know someone with better computer graphics skills than myself would cringe, but it got the job done. I suppose many of the people who use Excel for what they call a database work along similar principles.
This whole debate seems off point. Judging by the OP, he has a system in place that works. Somebody wants to replace it. So 1) why? what’s the problem with the thing as it is? 2) how do you replace it without understanding it? 3) who has approved the paid time being spent on this? And after these questions are answered, then you decide on what software to use to make the new system. Is it Access? Maybe. Reading this thread there are a huge number of options. Who’s deciding? Do they have sufficient knowledge of all the options? Then you make a new system and run it in parallel with the old system to make sure it works. And then there’s documentation? Backups? Excel vs Access seems the trivial part. (For the record I’ve done a lot of work with both. Not sure why there isn’t a combo product combining the two, unless it is a problem with calling it Excess.)
If the problem shape is “We like Microsoft Office desktop stuff, we’re familiar with it, but we need to run queries”, then Access is an OK solution. Wouldn’t be my choice, but it’s defensible depending on the constraints.
To the OP’s question of obsolescence, it’s being maintained, people are using it for the above appropriate niche (and some very inappropriate ones). But Access wouldn’t be a part of most people’s career path, or most department’s technical roadmaps.
That Excel is working as a ‘database’ for large company is, I think, perhaps a misunderstanding on the OP’s part. Or perhaps he hasn’t completely explained it. Excel is not a database.
What’s the problem with using a knife to turn a screw?
How do you switch from a knife to a screwdriver without understanding the screw first?
Who cares?
OK; that last response of mine was a little flippant, but “who said you could do this?” is well outside the scope of the question at hand on this forum. Also, it carries a whiff of overzealous hall-monitoring.
Like a knife, Excel is the right tool for only a few jobs; a database of parts (probably a BOM, or bill of materials) is not one of those. Also, you don’t have to know much about the specifics of a joint to know that driving a screw with a knife is probably a mistake. In desperate moments, one might try to turn a screw with a knife. But any other situation implicitly demands a screwdriver, even if the worker can’t tell that he’s bent the tip of his knife.
The OP complains that the person trying to effect this change comes from “a coding background,” which means it’s more likely that the change-effector knows this subject better than he OP and is therefore better-equipped to decide whether a true database is a good idea.
For what it’s worth, I’m in a field immediately adjacent to that of the OP: I’m a mechanical engineer who also dabbles in database administration/programming. I’ve seen some ugly spreadsheets created by people who don’t understand databases. None of them has ever seen any inadequacy in their spreadsheets. How could they?
Those of us gagging on the spreadsheet-as-database concept are essentially paraphrasing Samuel Johnson: Excel used as a database is like a dog walking on his hind legs. It is not done well, but you are surprised to find it done at all.
(N.B. The original quote is one of the most sexist assertions ever).
Since you’ve done a lot of work with both, you must be aware that you can already use access as a front-end to data stored in Excel spreadsheets. You must be similarly aware that you can read data from Access databases into Excel spreadsheets. But given that knowledge, your statement doesn’t make much sense unless you just wanted to make your joke about Excess. (In which case: cheers!)
There seems to be some consensus that even Access is probably a bad idea for most use cases, and that you either want a flat text file or a full SQL database. (Microsoft’s SQL Server product is just one of many “brands” of SQL databases. My first choice for a multi-user environment would be PostgreSQL, but MySQL and others would be fine too. Both are free. So is SQLite, which would be my first choice for a simple, single-user environment.
Is it a screw? That’s the whole problem isn’t it? We don’t know what the heck the spreadsheet is for.
Well, yeah. There are a lot of uses for Excel. If the OP is using Excel for anything other than a simple table, then the guy is looking at duplicating a lot of calculations and graphs and reports in Access. But this is all speculation.
Does the guy know what the existing system does? Does he understand it? Maybe he’s right, and it can all be done better in Access. Or he’s wrong and he’s going to throw out something that works and start over from scratch.
But we do know what the spreadsheet is for, and I alluded to it in the post you just responded to. To wit:
As I said before, the OP’s field is immediately adjacent to mine. This is probably a BOM or set of BOMs. It could also be a simple list of all components currently in production. Regardless, I’d argue that in the current metaphor, “a large spreadsheet of parts that is accessed by the whole company” is definitely a screw. BOMs are handled most robustly by product lifecycle management (PLM) systems, which are usually built upon SQL databases of some persuasion. This is likely a solved problem, and it was solved with databases.
Barring new and radically different information from the OP, a knife is the wrong tool for this job.
Or simply, wait for it, a list of parts, or of parts with their sources, or stock lists, or… It’s not a BOM unless each part is linked to its uses, which isn’t necessarily the case. And a lot of my customers who did have BOMs would have gone into hysterics if you tried to get them to implement PLM.
I think this is a key post. It sounds like the new guy wants to come in and replace one adhoc DB (for some definition of DB) with another one.
The replacement will probably be better for some definition of better, but unless it is usable/maintainable by whoever comes along after him, it isn’t actually better. It’s pretty easy to find someone who can hack an Excel spreadsheet - Access has never had much of a user base.
And there undoubtedly isn’t a budget to go to an actual IT group to develop a ‘real’ application.
Why? Because he likes Access better than Excel, so he’s using that to fix what is not broken.
I believe he is doing this as a sincere effort to improve our database, despite Excel being functional and easily maintained by all users
He has steamrolled the manager into believing this is an improvement that will benefit the entire community. It’s been several weeks of migrating the existing data into the new database and will require several more weeks to train all other users to adapt. In my experience, this leaves the company at the mercy of this one individual, hoping he doesn’t leave before training someone to maintain it in his absence. I just checked again - this company does not support this software with any training or updates, and there is no IT assistance available. I cannot begin to understand how he has accomplished this .
Upside? It’s making my decision to leave a lot easier.
You map the solution to the problem based on a number of key variables being optimized, the most critical of which is resources (time and money). Excel is simple with an extremely flexible UI (direct access to cells/rows/columns) which doesn’t require coding for managing most sets/tables of data. Building an app with database takes considerably more resources and more specialized resources.
Excel is the right tool for the job at a ratio of somewhere between 1,000 to 1 and 10,000 to 1 (in most medium to large organizations). Most of the tasks involve tracking small sets of data or it could be large but simple sets of data.
Regarding creating complexity and problems in Excel, yes it happens, but guess what? It also happens in databases and applications built by internal IT departments as well as even the commercial applications from market leading vendors. Those last ones are even more costly because you have to work around the poor design for a long time.
The only thing the OP said that could be a red flag is that the entire company uses it. Depending on the size of the company and the actual usage by “everyone”, it could be something that belongs in a more sophisticated container, but like pmwgreen said, we need more info about usage.