Using a Microsoft access DB .accdb on a linux server with Node.js

Question

So, I got this working on windows easy, and that is not surprising. But I am more concerned about running this on a production linux server.

Is there a way to get the Access Runtime 2013 to work on a linux based system without throwing some weird way of doing it. If not, then I am going to have to rethink my plans.

Just as a code base this is what I am doing:

const ADODB = require('node-adodb');
ADODB.debug = true;
//Bring in LUT Database
const LUTDB = ADODB.open(
  'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;Persist Security Info=False'
);
LUTDB.query('SELECT * FROM Table1')
  .then(data => {
    console.log(data);
  })
  .catch(err => {
    console.log(err);
  });

Answer 1

Nope.

The Access Runtime (as well as the database engine and full application) is only compatible with Windows, and barring Wine stuff/other trickery that's not production-worthy, it plain won't work.

There are third-party ODBC/JDBC drivers for Access out there that do work on Linux, but nothing official from Microsoft.

My recommendation would be: either don't use Access on Linux (best), or try UCanAccess with a JDBC wrapper (not affiliated). Note that the accdb file format has changed a number of times, so you can expect trouble/incompatibilities if you don't use an actively maintained product.

Answer 2

Like @ErikA suggests in his answer, if anyone is using Linux or MacOS, you can use BOTH:

1. The open source UCanAccess http://ucanaccess.sourceforge.net/site.html
2. Java library with the JDBC wrappers as package: https://www.npmjs.com/package/jdbc

I'm using MacOS Catalina. You can initialize a project with npm in a new directory:

npm init
npm i --save jdbc
mkdir index.js

Download and copy the 5 UCanAccess jar files:

• Main jar ucanaccess-5.0.1.jar
• All 4 jar files in lib directory

They can be put in the same directory too. I created a simple MS Access .accdb file with 1 table: employees (id, name, photo)

And, in index.js file, here is the codes to connect and SELECT to DB (most codes are from JDBC package, with a few modifications marked with // CUSTOM over the modified line):

var JDBC = require('jdbc');
var jinst = require('jdbc/lib/jinst');

if (!jinst.isJvmCreated()) {
  jinst.addOption("-Xrs");

  // CUSTOM
  jinst.setupClasspath([
    './drivers/ucanaccess-5.0.1.jar',
    './drivers/commons-lang3-3.8.1.jar',
    './drivers/commons-logging-1.2.jar',
    './drivers/hsqldb-2.5.0.jar',
    './drivers/jackcess-3.0.1.jar'
  ]);
}

var config = {
  // Required
  // CUSTOM
  url: 'jdbc:ucanaccess:///Users/Keitel/repos/mjsp/badge/badge-test.accdb',

  // Optional
  // CUSTOM
  drivername: 'net.ucanaccess.jdbc.UcanaccessDriver',
  minpoolsize: 10,
  maxpoolsize: 100,

  // Note that if you sepecify the user and password as below, they get
  // converted to properties and submitted to getConnection that way.  That
  // means that if your driver doesn't support the 'user' and 'password'
  // properties this will not work.  You will have to supply the appropriate
  // values in the properties object instead.
  // user: 'SA',
  // CUSTOM
  user: '',
  password: '',
  properties: {}
};

var hsqldb = new JDBC(config);

hsqldb.initialize(function(err) {
  if (err) {
    console.log(err);
    // CUSTOM
    return;
  }

  // CUSTOM
  console.log('successfully initialized');
});


// This assumes initialization as above.
// For series execution.
var asyncjs = require('async');

hsqldb.reserve(function(err, connObj) {
  // The connection returned from the pool is an object with two fields
  // {uuid: <uuid>, conn: <Connection>}
  if (connObj) {
    console.log("Using connection: " + connObj.uuid);
    // Grab the Connection for use.
    var conn = connObj.conn;

    // Adjust some connection options.  See connection.js for a full set of
    // supported methods.
    asyncjs.series([
      function(callback) {
        conn.setAutoCommit(false, function(err) {
          if (err) {
            callback(err);
          } else {
            callback(null);
          }
        });
      },
      // CUSTOM
      // Skip Schema selection with MS Access DB
    ], function(err, results) {
      // Check for errors if need be.
      // results is an array.
      if (err) {
        console.log('autocommit error', err);
      }
    });

    // Query the database.
    asyncjs.series([
      function(callback) {
        // Select statement example.
        conn.createStatement(function(err, statement) {
          if (err) {
            callback(err);
          } else {
            // Adjust some statement options before use.  See statement.js for
            // a full listing of supported options.
            statement.setFetchSize(100, function(err) {
              if (err) {
                callback(err);
              } else {
                // CUSTOM
                statement.executeQuery(
                  'SELECT id, name FROM employees;',
                  function(err, resultset) {
                    if (err) {
                      callback(err)
                    } else {
                      resultset.toObjArray(function(err, results) {
                        console.log('results', results);
                        callback(null, resultset);
                      });
                    }
                  }
                );
              }
            });
          }
        });
      },
    ], function(err, results) {
      // Results can also be processed here.
      // Release the connection back to the pool.
      hsqldb.release(connObj, function(err) {
        if (err) {
          console.log(err.message);
        }
      });
    });
  }
});

The results came out in terminal as:

results [
  { ID: 1, name: 'Keitel Jovin' },
  { ID: 2, name: 'Jetro Joseph' },
  { ID: 3, name: 'Bertha Bazile' }
]