Macro Scheduler 10.1 now has native database functions built-in, so the methods described below will no longer be necessary in most circumstances. However, read on if you are using an older version of Macro Scheduler, or have a need to access data sources from within VBScript code.
This document explains how to set up Microsoft ADO and use Macro Scheduler with VBScript to query and modify ODBC data sources. VBScript can query any ODBC data source using ADO (ActiveX Data Objects). With ADO and the appropriate ODBC drivers installed on your machine, simple scripts can be created to query and update your database using SQL.
The Microsoft Access Northwind sample database is used as an example.
Download & Install MDAC2.0 (Microsoft Data Access Components)
If you do not already have ADO installed on your machine, or you want to make sure you have the latest version of the Microsoft Data Access Components, you can download the setup program from http://www.microsoft.com/downloads/details.aspx?familyid=6C050FE3-C795-4B7D-B037-185D0506396C&displaylang=en.
Once downloaded, install the package by double clicking on the downloaded file. Installation is automatic.
Configure the ODBC Data Source
You will now need to set up an ODBC data source for your database. You may already have one configured. This example demonstrates how to create one for the Northwind.mdb sample database that ships with Microsoft Access.
1. In Control Panel, select ODBC.
2. Click 'Add'
3. Select the 'Microsoft Access Driver (*.mdb)'
4. When prompted for the Data Source Name, enter 'Northwind'.
5. Under Database, click 'Select', and locate the Northwind.mdb file from the Access\Samples directory.
6. Click OK. The ODBC data source is now created.
Writing the Script
Now we're ready to create a script to query the database. The following function performs a simple SQL query on the
Customers table of the database to return the Company Name associated with the given Customer ID :
Function GetCustomerName(CustID)
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Northwind"
SQLString = "select * from Customers where CustomerID = '" & CustID & "'"
set rsCustomers = MyDB.Execute(SQLString)
If Not rsCustomers.EOF then
GetCustomerName = rsCustomers.Fields("CompanyName")
Else
GetCustomerName = "Not Found"
End if
MyDB.Close
End Function
MyDB.Open "Northwind" creates a connection to the ODBC connection created above, where Northwind is the Data
Source Name. MyDB.Execute can be used to execute any SQL statement. So we could create a subroutine to modify the
customer name, as follows :
Sub ChangeName(CustID,NewName)
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Northwind"
SQLString = "Update Customers Set CompanyName = '" & NewName & "' Where (CustomerID = '" & CustID & "')"
set rsCustomers = MyDB.Execute(SQLString)
MyDB.Close
End Sub
Finally we can integrate these with Macro Scheduler. The following Macro Scheduler script simply prompts the
user for a Customer ID, returns the Customer Name and then allows the user to modify the Customer Name.
This also shows how to pass variables from Macro Scheduler code to VBScript code and back.
VBSTART
Function GetCustomerName(CustID)
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Northwind"
SQLString = "select * from Customers where CustomerID = '" & CustID & "'"
set rsCustomers = MyDB.Execute(SQLString)
If Not rsCustomers.EOF then
GetCustomerName = rsCustomers.Fields("CompanyName")
Else
GetCustomerName = "Not Found"
End if
MyDB.Close
End Function
Sub ChangeName(CustID,NewName)
Dim SQLString
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Northwind"
SQLString = "Update Customers Set CompanyName = '" & NewName & "' Where (CustomerID = '" & CustID & "')"
set rsCustomers = MyDB.Execute(SQLString)
MyDB.Close
End Sub
VBEND
Input>CustID,Enter Customer ID:
VBEval>GetCustomerName("%CustID%"),CustName
MessageModal>The Customer Name Is : %CRLF% %CRLF% %CustName%
Input>NewName,Enter New Name:,CustName
VBRun>ChangeName,%CustID%,%NewName%
To save time, you can Download The Script File and import it into your Macro Scheduler
set up. Download and then click New, then Import and locate the downloaded file and press OK.
For More Information
For further information about Microsoft ADO, have a look at http://www.microsoft.com/data. Also, check with your database vendor for updated ODBC drivers.
Check out our Scripts & Tips archive, for more examples, and view the VBScript Resource Page for more VBScript information.