Connect to MS Access Database with Javascript | HTA App

This one is a work in progress. The end goal is to build an easy scaffold for users to quickly build an HTA CRUD interface that connects to an MS Access database.
For those of you who don’t know what an HTA application is, you can read a very high level explanation here:
https://en.wikipedia.org/wiki/HTML_Application

HTA is wildly out of fashion. Most people don’t know what it is. There isn’t a ton of documentation out there but I have been playing around with it, and it is amazing. Because the HTA platform offers the same execution environment as a regular application and is not bound by the security restrictions of a web browser, it is the perfect platform for building local tools with web technology (HTML,CSS,JS).

The idea for this project came to be when I needed to build lightweight access to a shared MS Access database. The connection was slow so it would freeze and lock every time I tried connecting remotely. That’s even after breaking up backend and frontend, compressing the database, and disabling some unneeded features.

All I needed to do was to be able to update one field in one table which was such a simple task it drove me crazy. I didn’t have Visual Studio available so my only options were to use an Office application with VBA or build something in an HTA application.

I quickly built a UI with bootstrap, and wrote a dirty API in JavaScript with all presentation handled by jQuery. After the project was successful, I revisited the API and came up with a cleaner tool.

How to Use MS Access JavaScript DB Connection Script

1. Update the dbOptions object. Set the dbPath: to the file path of your access db.
2. Following the API code (this must be declared after the API Code), create a new instance of the MSAccess class:

   var myApp = new MSAccess(dbOptions);
   

   This method checks the connection settings and attempts to make a connection to your database. If you get an error, check the dbPath field to ensure you entered the correct path.

3. Methods:
• state: called with myApp.state will return the connection state to the database.
• runQuery(sql): called with myApp.runQuery("SELECT * FROM [table]") will return an array of javascript objects with query results. The format will follow:
  [{fieldname:"value",field2:"value"},{fieldname:"value",field2:"value"}]
• alertResults(sql): called with myApp.alertResults("SELECT * FROM [table]"), similar to runQuery(sql):, it takes a sql query as an argument and flashes an alert of results. This can be useful for debugging.

Access Database Connection with JavaScript (.accdb and .mdb)

/*!  
 * MS Access API for Javascript
 * By Ryan McCormick
 ***********************************************/

/* Declare Database Setup Options Here
************************************************/
var dbOptions = {
  dbPath: "sample-people.accdb",
  dbUserID: "",
  dbPassword: ""
};
/* MS Access API
 ************************************************/
var MSAccess = function(dbOptions) {
  this.dbOptions = dbOptions;
  this.myConn = new ActiveXObject("ADODB.Connection");
  this.connStr = "";
  this.sessionStr = "";
  this.connOption;
  var providers = ['Microsoft.ACE.OLEDB.12.0', 'Microsoft.Jet.OLEDB.4.0'],
    connError = [];
  // Test for connectivity
  for (var i = 0, x = providers.length; i <= x; i++) {
    var testConn = new ActiveXObject("ADODB.Connection");
    if (this.dbOptions.dbPassword.length > 1) {
      this.connStr = "Provider=" + providers[i] + ";Data Source=" + this.dbOptions.dbPath + ";Jet OLEDB:Database Password=" + this.dbOptions.dbPassword + ";";
    } else {
      this.connStr = "Provider=" + providers[i] + ";Data Source=" + this.dbOptions.dbPath + ";";
    }
    try {
      testConn.Open(this.connStr);
      if (testConn.State === 1) {
        this.connOption = i;
        this.sessionStr = this.connStr;
      }
    } catch (error) {
      testConn = undefined;
      connError.push(providers[i]);
    }
  }
  alert("Connection Successful with: " + providers[this.connOption]);
  // Start Connections
  if (this.connOption !== false) {
    this.myConn.Open(this.sessionStr);
    alert("Connection Successful");
  } else {
    this.myConn = undefined;
    alert("Connection Test Failed All Providers");
  }
};
/* MS Access API Prototypes
 ************************************************/
MSAccess.prototype = {
  state: function() {
    var status = "";
    switch (this.myConn.State) {
      case 0:
        status = "Not Connected";
        break;
      case 1:
        status = "Connected to " + this.dbOptions.dbPath;
        break;
      default:
    }
    return status;
  },
  runQuery: function(sql) {
    var results = [],
      fieldNames = [],
      rs = new ActiveXObject("ADODB.Recordset");
    // Send SQL to Build Recordset
    rs.Open(sql, this.myConn);
    // Collect FieldNames
    for (var i = 0, x = rs.Fields.Count; i < x; i++) {
      fieldNames.push(rs.Fields(i).name);
    }
    // Build Data Collection
    while (rs.eof === false) {
      var record = {};
      for (var z = 0, y = fieldNames.length; z < y; z++) {
        record[fieldNames[z]] = String(rs.Fields(z));
      }
      results.push(record);
      rs.MoveNext;
    }
    rs.Close();
    return results;
  },
  alertResults: function(sql) {
    var res = this.runQuery(sql),
      resultStr = "";
    for (var i = 0, x = res.length; i < x; i++) {
      // Print fieldnames if at beginning
      if (i === 0) {
        for (var r in res[i]) {
          resultStr += r + "\t";
        }
        // Move to new line
        resultStr += "\n";
        for (var p in res[i]) {
          resultStr += res[i][p] + "\t";
        }
        // Move to new line
        resultStr += "\n";
      } else {
        for (var q in res[i]) {
          resultStr += res[i][q] + "\t";
        }
        // Move to new line
        resultStr += "\n";
      }
    }
    alert(resultStr);
  },
  tblStruct: function(tbl) {}
};