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
    }

If you are using the ADO object to connect to a database within an HTA in a networked environment then you will get a rather annoying message box warning about ActiveXObjects.

To prevent this in all my HTAs I start the script off with the following statements (this is in Javascript).

var objShell = new ActiveXObject("WScript.Shell")
objShell.RegWrite("HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings\\Zones\\1\\1406", 0, "REG_DWORD")
objShell.RegWrite("HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings\\ZoneMap\\Domains\\myServer\\file", 2, "REG_DWORD")
objShell=null

The effect of the first RegWrite is to allow ‘Access data sources across domains’ and then the second write adds my network location (myServer) to the trusted sites.

These can be manually changed from within Internet Explorer as they are the zones settings in Internet Options.

Setting these keys requires no admin rights but they may well get overwritten by Group Policy so it’s best just to include them at the start of the script.

Note that the first time the application is run you will still get the warning as it will have read the old values, but by the next run they will be set.