36

I want to Access .mdb files and manipulate like insert / update using nodejs

Please suggest a library that would suite the need.

Thanks.

Beast
  • 617
  • 2
  • 8
  • 19
  • Is node's default file system library (http://nodejs.org/api/fs.html) insufficient for your needs? – Ari Jan 29 '14 at 10:05
  • how to insert / update into file using sql syntax ? – Beast Jan 29 '14 at 10:54
  • 1
    This is just for the POC, Thanks. Can you tell why you're preferring mongodb instead of MySQL ? – Beast Jan 29 '14 at 14:13
  • I searched this topic as a joke, anyways... To answer your last question. MongoDb can be run in a similar fashion as Access when we think about it';s portability. You can ship a project with the database right inside the directory structure HOWEVER, i'm assuming you are inheriting a legacy db and mongo is not a relational database so make sure that this is an option schema-wise ELSE maybe converting to MySQL will be a better option in the long run. But he is correct, you should be thinking about an alternative when working with node. I don't see a lot of support for MS access in nodejs. – Eric Bishard Jul 25 '15 at 08:13
  • 3
    @Mike Your comment provides no benefit to the discussion. If the poster had asked for recommendations two things would have happened: It would have received numerous MongoDB and CouchDB answers , and second it would have been closed as too subjective. He asked a perfectly reasonable question and not every project supports being able to replace an existing JET database regardless of how much we KNOW it's a bad choice. Pragmatic work vs. pie in the sky. There are many internal apps which still use Access for inventory tracking, CRM. And being able to expose a API to them is nice. – Andrew T Finnell Oct 25 '16 at 13:26

3 Answers3

30

Slightly different but node-adodb worked well for me for .accdb files:

https://www.npmjs.org/package/node-adodb

// Get the adodb module
var ADODB = require('node-adodb');
ADODB.debug = true;

// Connect to the MS Access DB
var connection = ADODB.open('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\dbs\\my-access-db.accdb;Persist Security Info=False;');

// Query the DB
connection
    .query('SELECT * FROM [TestTable];')
    .on('done', function (data){
        console.log('Result:'.green.bold, data);
    })
Rob Campion
  • 1,359
  • 13
  • 26
  • 10
    This only works if you are running Windows. It throws errors on a mac or linux OS because it requires MSAccess Drivers. If you do not have Access installed on your Win OS you can install just the driver here for 2010 http://www.microsoft.com/en-us/download/details.aspx?id=13255 – RandomDeduction Nov 03 '14 at 20:27
  • It's written in pure javascript. No node-gyp needed like node-odbc. Works perfectly! – Sam Jun 15 '16 at 07:36
  • 4
    It's nice when the question is answered and not criticised. There are numerous reasons to want to do this AND have no choice over replacing a JET database. This answer provides the solution to many database questions. I'm curious how it could be pure Javascript though. If it's true, then quite impressive accessing the ADO COM. – Andrew T Finnell Oct 25 '16 at 13:22
8

This article describes the process for connecting PHP to an Access .mdb database: http://www.sitepoint.com/using-an-access-database-with-php/

The process for Node.js is quite similar - it's just another ODBC data source.

You'll need a node ODBC package, such as: https://github.com/wankdanker/node-odbc

https://github.com/markdirish/node-odbc/

Then you'll need to format your ODBC connection string. eg.

"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=MyDatabase; Uid=; Pwd=;"
Community
  • 1
  • 1
Mike Causer
  • 8,196
  • 2
  • 43
  • 63
  • Will it work like path = "/path/to/the/file.mdb"; `"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="+path+"; Uid=; Pwd=;"` – Beast Jan 29 '14 at 14:09
  • It seems Dbq does support a path. – Mike Causer Jan 29 '14 at 14:13
  • 2
    Also, if you are using this driver with a 64-bit application (eg Node.js 64-bit) to access an .mdb or .accdb file directly, you may have issues. If you do, the following may be relevant: MS Office usually installs Microsoft Access Driver 32-bit which is incompatible with 64-bit apps. You'll need to 1) uninstall MS Office 2) download & install the Microsoft Access Database Engine 2010 Redistributable (x64) at http://www.microsoft.com/en-us/download/details.aspx?id=13255 3) install MS Office again – Ben Zuill-Smith Jul 30 '14 at 19:38
  • 1
    That sounds rather inconvenient – Mike Causer Jul 31 '14 at 00:55
4

My suggestion is OWIN module which is currently developed as Edge.js by Mr Tomasz Janczuk.

Michael Oskroba
  • 41
  • 1