How to Read and Write a Database from a Macro

There are several basic techniques that can be used to read/write databases. The exact technique used depends on the data source that must be accessed. There are three basic types of data sources that can be accessed with Active Call Center:

1. Data sources that store data in text file formats may be manipulated with text file read/write functions through the VBScript FileSystemObject.
2. Data sources that integrate with COM applications like MS Excel, MS Access, or Visual Basic may be manipulated by using the COM application as an intermediary between Active Call Center and the data source: Active Call Center sends requests to the COM application, the COM application sends the request to the data source, the data source sends the reply to the COM application, and finally the COM application returns the reply to Active Call Center.
3. Data sources that have COM interfaces can be used directly. This is by far the best and most reliable means of manipulating databases. Use this technique with Microsoft Access databases,SQL Server databases, and Excel spreadsheets just to name a few. One common technique is to use Microsoft's ActiveX Data Objects (ADO) to provide a COM interface to many other types of database formats. For more information on ADO, visit Microsoft's ADO site at: http://www.microsoft.com/data/ado .

Since the specifics of the first two types of data sources vary greatly, we will omit that discussion here. Sample code demonstrating a simple query of a Microsoft Access database from Active Call Center is shown below:

' Variables for database access.
' DAO would be similar. This is ADO
Dim adoConnection, adoRecordset
' Initialize the database connection.
Set adoConnection = CreateObject("ADODB.Connection")
' Open the database, use Microsoft Jet OLEDB data provider
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
AppPath & "\Examples\Database Example.mdb"
' Create a recordset object.
Set adoRecordset = CreateObject("ADODB.Recordset")
' Open the recordset.
adoRecordset.Open "SELECT * FROM LookupTable WHERE " + _
"SomeLookupNumber = " + Answer_Phone, _
adoConnection, 2, 3, -1
' Check if a value was retrieved.
If adoRecordset.EOF = False Then
' There is a value, tell the user.
Speak1 = "The following value was read from the database:"
Speak2 = adoRecordset.Fields("SomeText").Value
Speak3 = "Goodbye"
End If
' Close recordset and connection (this will happen automatically anyway).
adoRecordset.Close
adoConnection.Close

For more information on programming ADO and DAO through COM, refer to Microsoft's developer and support sites. An example Call Tree named "Database Example" is provided for further reference in the Examples folder.