The company I work for has evolved quickly from a small family company to being owned by a multinational corporation. We manufacturer "widgets" for the oil & gas industry.
Right now we use Excel to store the manufacturing data for our widgets. We have an excel "register" which contains the manufacturing data for around 20k widgets which we have built over the last 10 years or so. We have different families of widgets with each family's data being recorded in different sheets within the register workbook. Each worksheet has different column headers to reflect the different parameters associated with each type of widget.
Since we have grown rapidly, we now have around 20 manufacturing technicians, each of them building, testing and recording widget data. They record their data into a individual record sheets (Excel), which someone then has to copy the data from into the register. We have macros which do this, so the effort is not enormous, but I find it difficult to envisage a more "clunky" solution, which is bound to run into problems soon as we continue to grow. It is complicated slightly in that we also need to import some other data from external suppliers excel sheets, keyed by the serial numbers for some of the widget components. I already have a huge backlog of record sheets which I really need to transfer into the register.
One of the main issues was the requirement for users to store data concurrently, which is why they are generating their own individual record sheets. Concurrent, direct access to the data respository will be essential.
It is obvious to me that a database is the correct solution, but my question is what platform to build that on.
I have a little but of training on databases and can use SQL and VBA in equal measures - albeit not experienced in access specific vba. My first choice would be an SQL database with a web based front end, but I'm working with a couple of limitations. As we are in a corporate environment, our IT environment consists of shared network folders and "dropbox" style cloud file sharing areas, as well as a globally managed web-based intranet. I can therefore store files, but asking to get SQL server or Apache up and running on a server is proving difficult.
There is a "legacy" sharepoint area on our intranet, and I can request a site, but no idea if "legacy" means it is unsupported or could disappear at a moments notice, and I'd need to be able to administer the database myself - ie adding tables and columns etc, which I'm not sure how to set up.
I want to get something set up to start using from year end, so I think I am therefore limited to a MSAccess type database - albeit that access for me has an "image problem". That said, it may be the best solution in this instance.
I don't want users to have to "install" anything themselves, which means they are limited to either using an excel front end or a web based front end. I agree that a single MDB would be optimal, but I'd guess users would need to install access for that to work.
As much as I hate to admit it, it FEELS to me like my only available solution is to create an MDB back end with an Excel front end and I'm not convinced that would give me any better a solution than my current excel workbook with individual recrod sheets. I'm VERY open to other options though, but I'm struggling to see any here.
thanks for any input.
Guy