Connect to an Access Database and fill a combobox or SELECT control.
We are going to use the sample PCResale.NET Db from the Access Courses.
Firstly build a simple HTA like we have done previously.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Connect to Access db</title>
<HTA:APPLICATION
APPLICATIONNAME = "Fill ComboBox"
ICON = "599CD.ico"
/>
</head>
<body>
<h1>Connect to Access Db and Fill a ComboBox</h1>
</body>
</html>
We are going to get a list of Customers and place it on the Form.
Add a placeholder SELECT with an ID we can hook into.
We then need a script to connect to the Access database and display some information.
Create a Sub to do this. I'm going to call it FillCustomers().
Build up the variables we need, I like to Dim them and then Close them so I don't forget.
<script language="vbscript">
Sub FillCustomers()
'Connection String
Dim sConnect
'Command(s)
Dim objCmd
'RecordSet(s)
Dim objRS
'Clean Up and Close
objRS.Close
Set objRS = Nothing
End Sub
</script>
We need a Connection String to talk to Access. In this case I'm connecting to an ".accdb" which is 2007+
It can include the following attributes.
ProviderData Source
'Initialize Connection String to connect to an MS Access Database (.accdb)
sConnect = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=PCResale.accdb;Persist Security Info=False"
If this were an ".mdb" we would need a slightly amended string.
'Initialize Connection String to connect to an MS Access Database (.mdb)
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PCResale.mdb;Persist Security Info=False"
Notice that ACE has switched to JET and the version is 4.0 instead of 12.0
For the Source I'm using a relative reference as the DB is saved in the same folder as the HTA. You would need to put the full path if otherwise.
Next we can create a SQL statement which we can execute to return the Recordset of Customers.
'Create a command object to execute Advancded SQL Statements
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = sConnect 'Connect to database
objCmd.CommandText = "SELECT CustomerID, FirstName FROM CustomerT;"
Now create a RecordSet
' Create the actual recordset to be used in this script
' and retrieve or display information from database
Set objRS = CreateObject("ADODB.Recordset")
Set objRS = objCmd.Execute
Once we have our Recordset we need to add it to the combo. Lets do this in a simple loop.
While not objRS.EOF
Set objOption = document.createElement("option")
objOption.Text = objRS("FirstName")
objOption.Value = objRS("CustomerID")
document.PCResale.Customers.Add objOption
objRS.MoveNext
Wend
There are two ways you can access a Field, either by it's index (objRS(0)) or name (objRS("FirstName")).
To fill this value on the page you could either add a button to press or you can use a Load event.
I want it to show once the page has loaded.
By: 
