bobpeers -> blog

When writing HTAs to connect to an MS Access database I used to always use VBScript.
The obvious reason for this being that it seemed easier since both are Microsoft technologies plus I initially had no idea how to create or use ActiveXObjects from JavaScript.

After doing this for a while I became very frustrated at the limitations of using VBScript so looked deeper into changing to JavaScript and found that’s it’s actually very easy to use.

As a starter for anyone else in the same position here some framework code showing how to create an ADO connection, open a recordset and execute the command object all using JavaScript.
(I know technically this is JScript, which is Microsofts version of JavaScript)

// path to database
var DBpath="\\\\Server\\Path\\myDB.mdb"

// set up a few object constants
var adLockReadOnly=1
var adOpenForwardOnly=0
var adCmdText=1

// create and open a new connection (MSAccess)
var cnn=new ActiveXObject("ADODB.connection")
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;Data Source=" + DBpath
try
    {
    cnn.open
    }
catch(err)
    {
    // could not open connection
    // view details in err.Description and err.Number
    return 0
    }

//open a read only recordset
var rs = new ActiveXObject("ADODB.Recordset")
try
	{
	rs.Open("Select * from myTable", cnn, adOpenForwardOnly, adLockReadOnly)
	}
catch(err)
	{
	// could not open recordset
	return 0
	}
while(!rs.EOF)
	{
	// do something
	rs.movenext
	}
rs.close

//insert records with command object
var cmd=new ActiveXObject("ADODB.command")
cmd.ActiveConnection = cnn
cmd.CommandText = "Insert into myTables values(x ,y ,z)"
cmd.CommandType=adCmdText
    try
    {
    cmd.Execute()
    }
    catch(err)
    {
    // could not execute SQL
    return 0
    }