• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Connect to MS Access Database with Javascript | HTA App

November 21, 2015 by Ryan McCormick 4 Comments

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) {}
};

Related

Filed Under: Javascript, Microsoft Access Tagged With: ado, database connection, javascript, msaccess

Reader Interactions

Comments

  1. Prathap Stephen says

    February 17, 2017 at 6:11 am

    this does not work in chrome but only in ie browser. Do you know anyway to make it work in chrome browser?

    Reply
    • Ryan McCormick says

      March 19, 2017 at 3:53 pm

      Unfortunately this solution extends windows activex objects and does not work in Chrome.

      Reply
  2. George BatnaSon says

    November 24, 2017 at 6:43 am

    Where can we download the plugin to let Chrome and web browser recognize the MSAccess class ?

    Reply
  3. Om'r Bavaria says

    April 24, 2018 at 2:03 pm

    this is awesome !! i was thinking in 2 months of a method to store data in my html app using javascript but i couldn’t find any solution ! thank you soo much sir thank you 🙂

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in