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 = "Connect to Access db"
ICON = "599CD.ico"
/>
</head>
<body>
<h1>Connect to Access Db</h1>
</body>
</html>
We are going to get a list of Customers and place it on the Form.
Add a placeholder DIV with an ID we can hook into.
<body>
<h1>Connect to Access Db</h1>
<div id="myContent">
</div>
</body>
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 FillNames().
Build up the variables we need, I like to Dim them and then Close them so I don't forget.
<script language="vbscript">
Sub FillNames()
'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 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 a String. Lets do this in a simple loop.
Dim strCustomers
While Not objRS.EOF
strCustomers = strCustomers & objRS("FirstName") & ","
objRS.MoveNext
Wend
There are two ways you can access a Field, either by it's index (objRS(0)) or name (objRS("FirstName")).
Finally we need to set the value in the HTA.
We use the dot (.) notation to access a property of HTML element. (ID.PROPERTY)
myContent.innerHTML = strCustomers
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.
<script language="vbscript">
Sub FillNames()
'Connection String
Dim sConnect
'Commands
Dim objCmd
'RecordSets
Dim objRS
'Initialize Connection String to connect to an MS Access Database
'sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PCResale.mdb;Persist Security Info=False"
sConnect = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=PCResale.accdb;Persist Security Info=False"
'Create a command object to execute Advancded SQL Statements
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = sConnect 'Connect to database
objCmd.CommandText = "SELECT FirstName FROM CustomerT;"
' 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
Dim strCustomers
While Not objRS.EOF
'msgbox objRS(0)
'msgbox objRS("FirstName")
strCustomers = strCustomers & objRS("FirstName") & ","
objRS.MoveNext
Wend
myContent.innerHTML = "Names: " & strCustomers
'Clean Up and Close
objRS.Close
Set objRS = Nothing
End Sub
</script>
By: 
