ELN in Access
The lab part of my electronic lab notebook rebuilt as a Microsoft Access database, generated from the web app's data model.
Not every lab can run a web server. This project puts the lab part of PIR-ELN into a Microsoft Access database that can sit on a shared network drive: projects and tasks, formulations with versions and rule checks, experiments with recipes and results, stability studies, and the raw material catalogue with stock.
The repository holds two databases. The first is a one-to-one conversion of an existing Excel workbook of raw materials, recipes and test results, with the workbook's formulas turned into queries. The second is the lab notebook itself: 30 tables, 31 queries and 85 forms.
What was interesting to solve
- Generating Access without Access. I could not run Access in my build environment. So the database is built by a chain of Python scripts that write the schema and data to TSV files, and a small Java program that uses the Jackcess library to write the
.accdbfile. The forms and VBA modules are generated from metadata as well. - Importing real data. The build can read the lab part of an existing web-app database, so a team can move over with their data.
- Changing a database that is already in use. Big structural changes mean a new file. Small ones ship as updater modules that only add what is missing (a column, a table, a row) and never remove data.
- Things only real Access catches. VBA had to use late binding and by-value parameters, and some joins produced an "ambiguous field" error that my test tools never showed. Those were fixed by renaming ID columns in subqueries.
Stack
Microsoft Access, VBA, Python, openpyxl, SQLAlchemy, Java, Jackcess.