Reading a .mdb file

Hi,

I have an equipment that writes to an .mdb file. I believe that this is a type of database.

Can you suggest any node or any other way that I can read the latest entry in the file.

Thank you

MDB files come from Microsoft Access. You can get a copy by subscribing to Microsoft Office 365.

Alternatively, Libre Office should let you open them as should Kexi.

You should also be able to get ODBC or JDBC drivers to open them.

Hi,

I want to read the data directly in to node red. Is that possible.?

If so, can you share me any links/manual on how to set it up

thank you

There doesnt seem to be a node-red node to access Access :crazy_face:
since its not very commonly used and there are more modern dbs as a replacement but ..
doing a quick search on the internet reveals that there is an npm package called mdb-reader that can read those files.

So by using a Node-red Function and its functionExternalModules option and adapting the code example from the link :

[{"id":"f58e0f8db2538520","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":280,"y":3580,"wires":[["89431c89cd3fbf86"]]},{"id":"89431c89cd3fbf86","type":"function","z":"54efb553244c241f","name":"MDB Reader","func":"const { readFileSync } = fs;\nconst MDBReader = mdb;\n\nconst buffer = readFileSync(\"C:/share/PetStore2002.mdb\");\nconst reader = new MDBReader(buffer);\n\nconst tables = reader.getTableNames();\nconst table = reader.getTable(\"Animal\");\nconst columns = table.getColumnNames();\nconst data = table.getData();\nconst lastEntry = data[data.length - 1]\n\nmsg.payload = { tables, table, columns, data, lastEntry };\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"mdb","module":"mdb-reader"},{"var":"fs","module":"fs"}],"x":490,"y":3580,"wires":[["32f5e6abb8d5ca44"]]},{"id":"32f5e6abb8d5ca44","type":"debug","z":"54efb553244c241f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":3580,"wires":[]}]

image

image

3 Likes

There are also ODBC and JDBC nodes.

Hi,

I was trying your suggestion but I got stuck editing the settings.js. Im able to install the mdb-reader.


NodeRedError
the line "fs:require('fs')," works but I think the line "mdb-reader:require('mdb-reader')" because Im not able to start node red and it throws an error at this line.

For the fs though,Im able to see it in node red function node. but I did not install anything for the fs. Do I need to install anything?

I have actually never edited this portion of the settings.js file before .Can you please assist me further on this.

thank you

What version of Node-red are you running ?

Comment out the fs and mdb-reader lines from the functionGlobalContext section .. you dont need them.

The new feature of functionExternalModules: true would be enough if you are running a quite recent version of Node-red and the modules should auto-install when you deploy the flow i shared.

ps. make sure you restart NR after the settings.js changes

noderedversion
hi,
the version used is v2.0.6

ok .. thats fine .. the functionExternalModules feature was introduced in version 1.3.0

Error: port in use most probably you are running Node-red as a service and you tried to run it twice ?

Try from the terminal
node-red-stop
and then
node-red-start

Hi,
The node red was already.I just rerun it just to get the node red version.
I have reboot/power recycle my device. Node Red is running fine

But the modules are not visible in the function node setup.By default the functionExternalModules is already set to true. I tried searching online and it mentions to add the line in functionglobalcontext. However, it doesnt seem to work for mdb reader.

Any idea what could have gone wrong?

As i mentioned in my previous posts, functionGlobalContext was an older option .. it can still work but there is no need to use that way of using external modules because the newer feature functionExternalModules was introduced in Node-red version 1.3.0. You have v2.0.6 so that's fine.

great

thats strange .. you imported the flow that i shared above and you dont see the modules listed there ?
Can you show us a screenshot ?
Can you add them manually ? - although you wouldnt need to

hi,
actually if I import your nodes, the modules are visible in the function node setup.

But if I don't import, it doesn't show the modules automatically in the function node setup.

Let me try import, and see if I can read my mdb data..

thank you

Hi,

I tried. it gives error message for the modules.


I have included my nodes as a reference
flows (5).json (1.3 KB)

Is this because Im using nodeJs v12.?

if so is there a work around?

must be ..
it mentions on the npm site of the mdb-reader module that the supported version is node 14 and 16
image

What OS are you running Node-red on ? is it Linux based ?
Did you install node-red using the official bash script ? (link)

If yes .. you can add --node16 to the script to update your Node-red to the latest version 3.0.1 and also nodejs to v16.

Like this:

bash <(curl -sL https://raw.githubusercontent.com/node-red/linux-installers/master/deb/update-nodejs-and-nodered) --node16

ps. its good practice to make a backup of your flows first

Actually the device I'm using is a Siemens gateway device, IOT2050. In the Siemens website, an image file is provided. Upon loading it, all the software including NodeRed is installed automatically.
Current OS version used is linux Debian 11.

Let me try your suggestion and see what happens.

Thank you

I think its better not to !! better consult with Siemens support how to update you device properly .. cause it may have things that depend on specific versions and may break functionality

[EDIT]
that said .. there is a video of a person upgrading the IOT2050 with that command

Hi,

Upon upgrading the nodeJs to v16 and node red to v3.0.2(Exactly as per the youtube video), I am able to successfully read the mdb file using sample code provided.

Thank you very much. This helped a lot.

1 Like