Using Microsoft Access in a Modern Web Application

Our latest project at lab.SNG is the Register of Modern Architecture. A comprehensive database of notable modernist Slovak buildings and their authors, developed for the Department of Architecture at the Slovak Academy of Sciences.

The front page of the Register of Modern Architecture

The technical stack is also fairly modern: a Laravel project 7 with Telescope, Horizon and Scout, MySQL indexed in Elasticsearch. Latest Bootstrap on front-end, responsive images, customized Mapbox, modern JavaScript. And, well, the data comes from Microsoft Access.

Each building is tagged and indexed based on its authors, functions, decade, …

But Access is dead! you cry out. And you’re right. So why did we use it?

The answer is simple: resources. We could have built a complex content management system covering all the intricacies of maintaining a specialist database. But that would cost much more time, money, code and bugs. On the other hand, the Access database was already there. Ready, familiar and actively maintained by the good people at Department of Architecture.

We could have built a complex content management system […] But that would cost much more time, money, code and bugs.

Accessing the data

The initial hurdle with this approach was getting to the data. Here’s what we tried unsuccessfully:

• Getting the data from the Access file directly
  Does not work in non-Windows environments. Hard to set up for remote access.
• Reading from an XML export
  Laborious to trigger and difficult to operate remotely.
• Writing Access data to MySQL tables
  Greatly limits what you can and can’t do with your Access database.

Here’s what worked.

Access can export its data to a remote MySQL database using the MySQL ODBC connector. Once the data is in a MySQL database, we’re almost done! We can now query it easily and import whatever we need into a separate database for the web app.

With that (and pinch of Visual Basic), we created a simple Publish button, which lets the Access user publish their data on the website within minutes.

Want to publish your changes? Just press this button.

Here’s an overview of the process:

1. User presses the Publish button in Access.
2. Access dumps all of its tables into an intermediary upstream database.
3. Access sends an API message to Laravel, triggering the import process.
4. Laravel gets new data from the upstream database and re-indexes it.
5. Horray! Data is published on the web!

To see the process in more detail, the entire source code of the project is on GitHub: https://github.com/SlovakNationalGallery/register-architektury

That includes the source code for the Publish button, and the source code for the Laravel import job.

Detail page for a building. Look ma, no CMS!

I hope you liked this short write-up. If you have any question or comments, write to us here, or at lab@sng.sk.

Here’s an incomplete list of open-source projects we used and loved in this project:

• Laravel (and its wonderful ecosystem)
• babenkoivan/scout-elasticsearch-driver
• spatie/laravel-medialibrary
• spatie/laravel-translatable
• JeffreyWay/laravel-mix
• Backpack for Laravel

Thank you! ❤