Get data from Microsoft Access .mdb database

We have several groov EPIC units set up in our plant controlling various aspects of our equipment (e.g. feedrate, time, temperature).

20 years ago, we started using a Microsoft Access (.mdb) database to store each “recipe” (feedrate, time, temperature) of each part that we run. We are migrating this to SQL right now (still a few months away), and I know Node-red will offer us some nice connectivity options when that happens. But for now, all I want to do is query the Access database. I have searched this forum and others for how do to this, but the answers all seem either very complicated or require a special Windows only driver or something else.

Here’s where we have some flexibility…The small subset of data that we actually need from the database can be sent out once an hour as a .csv, for example, and place on shared network drive. It would be very simple, e.g.

Work order number, feedrate, time, temperature

76434, 350, 1:30, 1580
76435, 500, 2:05, 1650
76436, 400, 3:15, 1520

I could then set up our strategy to have the operator enter the work order number and then the 3 values (feedrate, time and temperature) would be pulled in and populated as variables.

What is the simplest way to accomplish this?

Do you have a Windows PC in the mix somewhere?
If so, I would run SoftPAC on it, because it is running on the PC it can read the recipe file off the network drive and then peer-to-peer the data to each EPIC.

You could connect your access database to SQL server as a linked server as an interim step in your migration. You could then utilize node-red now against the SQL server which will “pass through” to the access database.

You could use SQL Express edition for this too. If you go this route, make sure that you download and use the 64 bit access ole db driver (assuming you are using a 64 bit edition of SQL Server).

I actually came to the same conclusion! Since we are in the process of migrating our Access database to SQL Server, we have in fact already set up everything and are just working on stored procedures, new features, etc. But it’s easy enough to create another table containing the relevant Access data, and then run an import routine every hour or so. Then use Nod-red to query the SQL server (which I am educating myself now on).