I don't have a problem so much as hoping for advice or ideas to improve my system;
(I am aware there are commercial solutions available for this very application, think of what I am doing as a proof-of-concept to investigate whether it would actually help before we spend time and money on implementing a 3rd party solution)
I have developed an asset tracking system to implement at the factory where I work. Operators will scan a couple of bar-codes relating to the activity they have just completed, and the component they have done it to. These bar-code scans are collected by an Access DB which feeds into an Excel dashboard for management to give them an overview of where in the process parts are and overall order status.
We are about to trial my system, and I am just getting cold feet that there are flaws which could be addressed now before it goes 'live'.
Currently it is set up as follows;
Although I am confident the DB and all workbooks have adequate protection to prevent users messing with them (deliberate or not!) - I am very much aware that all it would take would be the deletion/movement/renaming of one of these utility workbooks to cause havoc with my system. Making them Read-only is not an option since unbeknownst to the user, the macro is frequently writing to a hidden sheet and saving the workbook.
Can anyone suggest how this workflow could be made more robust? Is it possible for Excel to write directly to the database table without the users having Access installed on their machines - or a method of writing to Access directly without using Excel at all, such as with Outlook or HTML.
Thank you for reading. Any advice or suggestions are welcome!
John
(I am aware there are commercial solutions available for this very application, think of what I am doing as a proof-of-concept to investigate whether it would actually help before we spend time and money on implementing a 3rd party solution)
I have developed an asset tracking system to implement at the factory where I work. Operators will scan a couple of bar-codes relating to the activity they have just completed, and the component they have done it to. These bar-code scans are collected by an Access DB which feeds into an Excel dashboard for management to give them an overview of where in the process parts are and overall order status.
We are about to trial my system, and I am just getting cold feet that there are flaws which could be addressed now before it goes 'live'.
Currently it is set up as follows;
- Multiple bar-code scanning utilities (Excel) - a very basic UI with a 'scan good' & 'scan bad' button. Each launches a macro to capture their barcode scans, apply a timestamp and a "good"/"bad" variable. This record is saved to a hidden sheet and the workbook saved.
- Database (Access) - linked to each of these 'utility' workbooks and a Union query to append them to a single table.
Although I am confident the DB and all workbooks have adequate protection to prevent users messing with them (deliberate or not!) - I am very much aware that all it would take would be the deletion/movement/renaming of one of these utility workbooks to cause havoc with my system. Making them Read-only is not an option since unbeknownst to the user, the macro is frequently writing to a hidden sheet and saving the workbook.
Can anyone suggest how this workflow could be made more robust? Is it possible for Excel to write directly to the database table without the users having Access installed on their machines - or a method of writing to Access directly without using Excel at all, such as with Outlook or HTML.
Thank you for reading. Any advice or suggestions are welcome!
John
