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
- Update the
dbOptions
object. Set thedbPath:
to the file path of your access db. - 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.
- Methods:
state:
called withmyApp.state
will return the connection state to the database.runQuery(sql):
called withmyApp.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 withmyApp.alertResults("SELECT * FROM [table]")
, similar torunQuery(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) {}
};
this does not work in chrome but only in ie browser. Do you know anyway to make it work in chrome browser?
Unfortunately this solution extends windows activex objects and does not work in Chrome.
Where can we download the plugin to let Chrome and web browser recognize the MSAccess class ?
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 🙂