Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon  
 
NEW: Excel Expert 11 - Learn XLOOKUP    dismiss
Tips & Tricks

New Tips Added Weekly!
Click here to get on our Mailing List

   
 
HTA

Advanced - Connect to an Access Database

Access Database

Connect to an Access Database

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.

      Provider
      Data 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.

	<body onload="FillNames()">
	</body>
	
Final
HTA
	<!DOCTYPE html>
	<html lang="en">
	<head>
	   <title>Connect to Access db</title>
	   
	   <HTA:APPLICATION
			APPLICATIONNAME = "Connect to Access db"
			ICON = "599CD.ico"
		/>
	</head>

	<body onload="FillNames()">
		
		<h1>Connect to Access Db</h1>
		<div id="myContent">
		</div>

	</body>
	</html>
	

Script
	<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>
	

Access HTA


Sample Files
Access.hta
PCResale.accdb


Index: Index | Lesson: Google Chart

 

Alex Hedley (Avatar) By: Alex Hedley


Click here to sign up for more FREE tips

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search The Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed