MS Access Application over Oracle DB with VBA and SQL
Background
Even if today the popularity of MS Access is decreasing rapidly, actually, when you need a closed network desktop database application which can also connect almost all kind of RDBMSs available, I think it’s still unmatched!
When I started work in automotive in 2011 as a product development engineer, I was responsible for new product launches and handing over the projects to the serial production teams after their launch. Beside mechanical engineering skills, this role required a lot of data analytics and reporting skills. Because each engineering release needs to be tracked, approved technically when it’s mature enough and when I was leading the change management meetings, I needed to see everything in one place, quickly. Yet, every piece of information was scattered each department’s own systems or different parts of engineering release system and common material management system (ERP).
Before I joined the team, people tried to manage it with VBA powered Excel’s with complex macros which was not flexible at all. Also, most of the changes in the DBMS were directly crashing them.
I also handled the work with macros, in the beginning and yet , requirements were changing so quickly and apparently, we needed a more advanced system and way of working.
So with the great mentoring of the senior industrial engineer in the team, I created a complex database structure with multiple role screens and automated data flow from all systems, merging with our decision making algorithm, engineering package launches with hundreds of parts became very easy and mistake proof in 2 years.
To ensure not to share any kind of internal company information, in this article, I hid all the text information about the system in all of the images. This is just a high level overview to explain my work as a showcase of a skill. Product pictures are already public images, so I believe it’s OK to let them be.
User Authorization and Structure
There was not a built-in role based comprehensive function of Access to use. Therefore, I have designed VBA based authentication through an intro form.
When user entered the information, actually it’s creating a flow between server and client side. Instead of creating a single MS Access DB, I have created one main DB as a server to all other client MS Access DB files.
In this approach, you need to also have a status checker in all client versions about a maintenance mode. Because, whenever you need to change something in the main DB, first all users needs to be logged out. I created a maintenance status checker on client sides which automatically close all clients in defined minutes.
All of the editing functions was locked on client sides. Even the ‘shift + enter’ doesn’t work because the files were in ACCDE format which is only the “compiled version” of the client database files.
Why This Structure?
Due to the need for combining our own tables with information obtained from various data sources such as Oracle DB tables using SQL and the IBM PCCOM user interface, the size of the database was becoming excessively large. Moreover, distributing this information and executing queries on each user’s side imposed a significant burden on the main servers.
Instead, I devised a solution by creating a datamart, which comprised intermediate view tables that were automatically updated at specified intervals. As a result, the users of our application no longer needed to directly access the primary raw data sources. They could exclusively access data that had been cleaned, filtered, sorted, and made ready for their specific tasks.
It is worth mentioning that in today’s cloud-based systems, there are numerous alternative solutions available for addressing such problems. However, during that time period, even the concept of cloud storage and computing was relatively new.
Capabilities
Over 50 Screens
In the application, there were over 50 different interfaces, each dedicated for different purposes from data entry screens to reporting and administration screens. It is designed as a full-stack solution tailored specifically engineering change management purposes.
Dash-boarding and Automated Reports
The system not only collects data from diverse sources but also processes it to derive meaning and initiates relevant actions by generating automated reports sent through MS Outlook or displaying warnings within the system. Additionally, it incorporates various types of dashboards and reports with customizable options.
Enhancing Team Communication
When there is no MS Teams around, there was team speak inside this system. 😁
It was kind of an old school ‘chat room’ (people who knows what mIRC also knows what is a “chat room”, haha) which only for quick log from different users sometimes coming or working in different schedules to let them put notes on the board which will notify others about the updates or issues. Of course, it wasn’t blocking people to send their birthday messages, too. 🥳
Conclusion
Thanks to the system I developed under the excellent guidance of my manager, our working methods underwent significant improvement. This allowed us to shift our focus from repetitive data tasks to more technical and engineering matters. Additionally, the system played a crucial role in minimizing errors, enhancing communication, and ultimately making a positive impact on the entire team.
This is also a demonstration of how a basic desktop database can turn to a powerful full-stack solution with some hard work and a little imagination!