database selection

[WinterDeere]

Hi folks,

Nothing to do with tractors, but with a wide and varied group here, I'm guessing there are a few who could offer me some advice here.

I implemented an ERP system for a small manufacturing business in Excel, a collection of about a dozen spreadsheets with error checking and a huge array of interdependencies. Some are simple match / lookup, but more are complicated operations based on finding data in other sheets. It all works great, but the latest versions of Excel are consistently breaking it with poorly-implemented autocorrect features, and of course it can only ever be accessed by one person at a time. It's also very prone to a user hosing the whole thing up by accident.

It's time to convert it to a database, but the only databases I've ever personally coded / created from scratch were all in MS Access. I'm not sure that dozens of hours put into recreating this ERP system in Access would be the best investment, but I don't know.

Of course there are canned ERP systems I could buy, but the cheap ones are very limited, and this operation is too small to invest in a big enterprise level solution and the investment that maintaining those usually entails. A fully-customized system in a very user-friendly database such as Access does seem to fit the bill.

Maximum users will likely remain under 10 for at least several years to come, and number of records (items < 5000, BOM's < 500, transactions < 20,000) isn't exacty huge, but it is sufficient to slow down Excel with the constant cross-referencing.

Go Access, or find and learn a better option?

 

[riptides]

Hi folks,

Nothing to do with tractors, but with a wide and varied group here, I'm guessing there are a few who could offer me some advice here.

I implemented an ERP system for a small manufacturing business in Excel, a collection of about a dozen spreadsheets with error checking and a huge array of interdependencies. Some are simple match / lookup, but more are complicated operations based on finding data in other sheets. It all works great, but the latest versions of Excel are consistently breaking it with poorly-implemented autocorrect features, and of course it can only ever be accessed by one person at a time. It's also very prone to a user hosing the whole thing up by accident.

It's time to convert it to a database, but the only databases I've ever personally coded / created from scratch were all in MS Access. I'm not sure that dozens of hours put into recreating this ERP system in Access would be the best investment, but I don't know.

Of course there are canned ERP systems I could buy, but the cheap ones are very limited, and this operation is too small to invest in a big enterprise level solution and the investment that maintaining those usually entails. A fully-customized system in a very user-friendly database such as Access does seem to fit the bill.

Maximum users will likely remain under 10 for at least several years to come, and number of records (items < 5000, BOM's < 500, transactions < 20,000) isn't exacty huge, but it is sufficient to slow down Excel with the constant cross-referencing.

Go Access, or find and learn a better option?

Seems like a quick fit for MS Access. You could solve your issue with numbers of spreadsheets, just using a shared Access database. Automate the input, automate the updates if someone does it in Excel. How big are the spreadsheets? You have a IIRC 2GB limit for Access. You may need to compress the db after input.

You get forms and functionality with Access. Plus there is a nice upgrade path to SQL server you can do should it "take off". Queries are easily built and prototyped. All in one place too, LOL.

Personally linking more than 2 or 3 spreadsheets is my cut off point for databases. That and number of records in spreadsheets.

Good Luck.

 

[WinterDeere]

Thanks! This is exactly the sort of advice I need.

The largest half dozen spreadsheets are only 450 kB, which is actually a surprise to me, given how large the arrays are, and how slowly some of them update off SSD-cached NAS thru 1 Gbps link. The total ERP system, 15 spreadsheets, is under 3 MB total storage. Hard to believe, but I guess text just doesn't take up that much storage, when we're all so used to looking at the size of video files and large images.

I guess another benefit of Access, over non-MS products, is that there may be more tools to automate the migration from Excel to Access.

Any issues with Access, that might frustrate efforts to share this with off-site contractors thru NAS share or cloud hosting?

 

[Torvy]

Flashbacks to my former life before teaching.

It's always a conundrum between going with what you have and what would likely be better.

It is 100% possible to set up Excel in a way that allows multiple people to enter information, but it is a little clunky. Access is better, but some staff can't wrap their heads around it. You'd probably be best off with a dedicated program designed for your purposes.

That said, at 10 users I would personally make Excel work for now (I tend to prefer something where I'm an expert rather than learning something new.)

You said 20k transactions...a day, a year?

I feel you on the newer versions...its been a problem since I used Lotus 123 under DOS.

Without diving into your setup, I would guess you could streamline what you have. My last role where I needed that level involved tracking work through various stages of completion. Each 'piece' might have up to 10 users who may need access at a given time. Typically, only 1 or 2 would be making changes as the work moved through the process.

Excel has become considerably more capable over the years, especially once you could run VB routines.

If my oldest wasn't gainfully employed, I'd refer you to him. He's a ChemE and has a head for that stuff.

You may be able to find a young enterprising person who can revamp your system. Even when/if you move on from Excel, someone is going to need to build it and transition your data.

Have fun! I used to love diving into that stuff. If I could fix my equipment in a spreadsheet, I'd be a great mechanic! Unfortunately, those skills don't translate.

 

[riptides]

Thanks! This is exactly the sort of advice I need.

The largest half dozen spreadsheets are only 450 kB, which is actually a surprise to me, given how large the arrays are, and how slowly some of them update off SSD-cached NAS thru 1 Gbps link. The total ERP system, 15 spreadsheets, is under 3 MB total storage. Hard to believe, but I guess text just doesn't take up that much storage, when we're all so used to looking at the size of video files and large images.

I guess another benefit of Access, over non-MS products, is that there may be more tools to automate the migration from Excel to Access.

Any issues with Access, that might frustrate efforts to share this with off-site contractors thru NAS share or cloud hosting?

Ok, sounds like the Excels are very small. More Excel code and formatting I guess. They should not be an issue. Access uses Visual Basic, as do all MS Office products. The good majority of functions "macros" in Excel if your under the hood can map directly to Access functions.

Getting your Excel spreadsheets into Access can be accomplished through either VBA code with the transferspreadsheet functions and code, or through the Access User Interface, get external data.

Backend infrastructure for Access can get a little tricky. If you have a network share, say LAN oriented, then obviously granting permissions to the database location requires the user to be on the LAN Domain. That's good for 5 to 25 people.

Moving to the cloud means cloud authentication and ensuring the location that the database is stored at/on is accessible via the cloud domain and network infrastructure. In addition the database should be stored on a "file" like infrastructure and not abstracted to or run under binary object stores.

A quick virtualized Windows File Server under Azure/Amazon/or your Cloud Provider will suffice.

Good Luck.

You can PM me for more details or consults at your leisure.

 

[anomad]

Are all the spreadsheets separate files? Combining the spreadsheets into one with multiple tabs and cleaning up your queries would be a simple way to start making incremental improvements. Can the organization host on Sharepoint/One Drive to facilitate sharing between multiple users?

I'm not thinking of any obvious benefits to go to Access over Excel if that is working for them now. I'd focus on the low hanging fruit before starting over, unless there is a clear reason to adopt a whole different solution.

 

[zzvyb6]

I use an engineering & scientific program tool (Matlab) to process, retrieve & store records into an Access database. Matlab has a Databas toolbox that lets me interact with Excel and Access via a GUI interface that is remarkably simple to construct using the Matlab GUI builder. This has served me well for analyzing data for statistical, prediction, model fitting, and graphical analysis. Whatever you choose, a 'relational' database is what you want so you can add new fields at any time without redesigning the whole project.
All this in about 15 lines of code using FB marketplace data.

 

[WinterDeere]

Are all the spreadsheets separate files? Combining the spreadsheets into one with multiple tabs and cleaning up your queries would be a simple way to start making incremental improvements. Can the organization host on Sharepoint/One Drive to facilitate sharing between multiple users?

I'm not thinking of any obvious benefits to go to Access over Excel if that is working for them now. I'd focus on the low hanging fruit before starting over, unless there is a clear reason to adopt a whole different solution.

15 spreadsheets, each with maybe 4 to 6 tabs. A fairly sizeable chunk of data. And there are good reasons for keeping it in separate files, regarding potential data corruption and restoration. Plus, we keep financial transactions in separate files from item master and BOM's, as well as stock room, to keep access restricted appropriately.

 

[Whittsend]

Hi folks,

Nothing to do with tractors, but with a wide and varied group here, I'm guessing there are a few who could offer me some advice here.

I implemented an ERP system for a small manufacturing business in Excel, a collection of about a dozen spreadsheets with error checking and a huge array of interdependencies. Some are simple match / lookup, but more are complicated operations based on finding data in other sheets. It all works great, but the latest versions of Excel are consistently breaking it with poorly-implemented autocorrect features, and of course it can only ever be accessed by one person at a time. It's also very prone to a user hosing the whole thing up by accident.

It's time to convert it to a database, but the only databases I've ever personally coded / created from scratch were all in MS Access. I'm not sure that dozens of hours put into recreating this ERP system in Access would be the best investment, but I don't know.

Of course there are canned ERP systems I could buy, but the cheap ones are very limited, and this operation is too small to invest in a big enterprise level solution and the investment that maintaining those usually entails. A fully-customized system in a very user-friendly database such as Access does seem to fit the bill.

Maximum users will likely remain under 10 for at least several years to come, and number of records (items < 5000, BOM's < 500, transactions < 20,000) isn't exacty huge, but it is sufficient to slow down Excel with the constant cross-referencing.

Go Access, or find and learn a better option?

I think you might run into some of the same issues with Access that you see with Excel. MS likes to bloat their products with tools and tweeks that usually cause more troubles than they solve. If I was doing this, I would look for a more long term fix. The world has changed since I was doing DBA and I am sure the market for DBs has changed a lot. I would look around for something that wouldn't be som prone to the bloating updates that MS seems to do with Office. Not to mention that I think the current version of Access is cloud based and not local.

 

[anomad]

15 spreadsheets, each with maybe 4 to 6 tabs. A fairly sizeable chunk of data. And there are good reasons for keeping it in separate files, regarding potential data corruption and restoration. Plus, we keep financial transactions in separate files from item master and BOM's, as well as stock room, to keep access restricted appropriately.

That sounds a little more complicated than I was thinking.

Is the spreadsheet constantly breaking the biggest problem?