VBScript – Sample Microsoft Office Access ( MS Access ) App

Background

Here is a code snippet for using VBScript to develop a small application that connects to MS Access database.

BTW, the MS Access database was created using the Create Database Wizard in Access.

Code

Script



option explicit

on error resume next

Dim CONNECTION_STRING
Dim objConn
Dim objRS

Dim strRow
Dim strData

Dim strLog

Const FILE_FOLDER="database\"
Const FILE_NAME="AddressBookDatabase.mdb"
Const PROVIDER="Microsoft.ACE.OLEDB.12.0;"

strData = ""

CONNECTION_STRING = "Provider=" & PROVIDER & ";Data Source=" & FILE_FOLDER & "" & FILE_NAME

strLog = "Connection String :- " & CONNECTION_STRING & vbCrLf
wscript.echo strLog
	
'Define object type
Set objConn = CreateObject("ADODB.Connection")
 
'Open Connection
objConn.open CONNECTION_STRING

if (Err.Number <> 0)  Then

	strLog = ""
	strLog = strLog & "Error" & vbCrLf
	strLog = strLog & "====" & vbCrLf	
	strLog = strLog & vbTab & "Connection String :- " & CONNECTION_STRING & vbCrLf
	strLog = strLog & vbTab & "Error Number :-  " & CSTR(Err.Number) & vbCrLf
	strLog = strLog & vbTab & "Error Description :-   " & CSTR(Err.Description) & vbCrLf
	
	wscript.echo strLog
	
	WScript.Echo vbTab & "Press [ENTER] to continue..."

	' Read dummy input. This call will not return until [ENTER] is pressed.
	WScript.StdIn.ReadLine
	
	WScript.Quit 

end if
 
Dim objFields
Dim iFieldCount
Dim iFieldID

'Define recordset and SQL query
Set objRS = objConn.execute("SELECT * FROM Addresses")
 
Set objFields  = Nothing
 
'While loop, loops through all available results
DO WHILE NOT objRS.EOF

	if (objFields is Nothing) Then
	
		Set objFields = objRS.Fields  

		iFieldCount = objFields.Count
		
	End if	
	
	strRow = ""
	
	'add data delimited by Tabs
	strRow = objRS.Fields("AddressID") & "" _
				& vbTab & objRS.Fields("FirstName") & "" _
				& vbTab & objRS.Fields("LastName") & "" _				
				& vbCrLf

	strData = strData & strRow
	
	'move to next result before looping again
	'this is important
	
	objRS.MoveNext
	'continue loop
	
Loop
 
'Close connection and release objects
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
 
'Return Results via MsgBox
MsgBox strData

Invoke


C:\Windows\SysWOW64\cscript.exe getDataMSAccess.vbs

Output

Output – Good

Output – Failure

Error Description :- Provider cannot be found. It may not be properly installed
Error Number :- 3706

 

Source Control

GitHub

Posted to GitHub here

 

Things to keep in Mind

OLE-DB Provider

As the OLE-DB Provider is 32-bit, on a 64-bit platform force script to run in 32-bit mode by explicitly referencing C:\Windows\SysWOW64\cscript.exe.

Syntax

C:\Windows\SysWOW64\cscript.exe [vbScriptFi<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>le]

Sample Invocation

C:\Windows\SysWOW64\cscript.exe getDataMSAccess.vbs

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s