Is there a ACE.OLEDB alternative?

[brusk]

I've received help several times over the years with one of the nasty workbook I created and managed so thank you everybody that's helped. The workbook collects via Forms customer server details such as network, hardware, user and other environmental and software details in tables on hidden sheets. From there I use extensive ACE SQL queries to combine and filter this information to the unhidden tables on other sheets. Over the past few years with different IT images, versions of O365 and other issues using this method has become very unreliable. I've gone down the rabbit hole with help here fixing a hand full of them and every time I think things are good a few users pop up with new issues months later. Are there any alternatives that will not require the end users to install anything new on their laptop since IT locks everything down. I'm hoping not to redo everything that loops through all the tables creating arrays etc as some of the queries would have multiple inner joins and such.

 

[Micron]

You can drive nails into wood with pliers but a hammer is the right tool for the job. In other words, all of that is a job for a relational database, not a workbook. Don't feel bad though as you're not alone in making that mistake. If you were to use Access then only the developer needs a license for that; everyone else can use the free run time version. If your IT objects to that, then why would they allow you to have Excel?

Anyway, the first thing you'd need to do with respect to creating a real database is to forget everything you know about spreadsheet design because db tables should not be designed as spreadsheets. You would have a significant learning curve ahead of you, and I would suggest a certain block of reading material at the outset. As you go, you would be wise to seek feedback on each of your next planned steps. If I were you and ran up against an IT roadblock I'd be trying to get someone who has more influence than me to get involved in getting around that. If I presented a case that the current tool is unmanageable and unstable and there is an alternative but can't get support from my management to push IT I'd push the problem onto that guy. "OK Mr. Manager, I've done my research, this is what it tells me we should do but if you're saying that can't happen, then how do you suggest we fix it?" I'd put the problem where it belongs.

 

[brusk]

Thanks for the response. It looks like I'm going to have to convert this monster to a web page and SQL. Currently it's over 12K lines of code for all the forms, classes and procedures to collect all the data. Unfortunately it's in Excel as my boss 10 years ago wanted it to be in something that could be offline to be completed in the office, at home, at the customer site or on a plane while traveling there. Also had to be accessible by internal employees or external partner resellers. They are trying to take all the information and put it into salesforce but our internal developers are slow, lack the ability to understand what we are doing and refuse to reach out for assistance. Therefore it will probably take them the next year to complete their work. In the meantime I was thought I'd check one more time for an internal excel approach.