A common task is to access an external database, often to store data received from a Session.

The easiest way to do this is to use Microsoft ActiveX Data Objects (ADO or "ADODB"). See that link for full documentation from Microsoft.

The entry point for using ADO is instantiate an ADODB.Connection object:

Set conn = CreateObject( "ADODB.Connection" )

Opening the Database

The ADODB.Connection object then needs to be opened. This is done using a connection string, which is an ADO concept and will vary based on the type of database you wish to connect to.

For a Microsoft Access database stored as an .mdb file on the local filesystem:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\Database.mdb"

See the documentation for the ADODB.Connection object for other ways to open the connection.

Opening a Table

Database tables (or, the results of a query) in ADO are managed by the ADODB.Recordset object. See that link for full documentation from Microsoft.

It can be instantiated in the usual fashion:

Set rs = CreateObject( "ADODB.Recordset" )

Then, a table from the connection conn can be opened:

rs.Open "SomeTableName", db, 3, 3, 2

See the documentation for the ADODB.Recordset object for a full explanation of the Open() method, and the meaning of the "magic numbers". The last parameter, 2 in this case, is of particular interest:

• 2 specifies that the first parameter is the name of a table to open, as in this case
• 1 can be used instead if the first parameter is an SQL query

Inserting a Row

For example, to insert some data from a Session into a database when the session is received:

Function OnIncomingSession ( Session )

Set conn = CreateObject( "ADODB.Connection" )
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\Database.mdb"

Set rs = CreateObject( "ADODB.Recordset" )
rs.Open "SomeTableName", db, 3, 3, 2 ' (2) because we want to open a table by name

rs.AddNew ' Start a new row

rs("UnitID") = Session.UnitID
rs("PointName1") = Session("PointName1")
rs("PointName2") = Session("PointName2")

rs.Update ' Insert and close
rs.Close

OnIncomingSession = True

End Function

Updating a Row

Alternatively, an existing row could be updated, where the row is specified by a value in the session:

Function OnIncomingSession ( Session )

Set conn = CreateObject( "ADODB.Connection" )
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\Database.mdb"

query = "SELECT * FROM SomeTableName WHERE CustomerName='" & Session("CustomerName") & "'"

Set rs = CreateObject( "ADODB.Recordset" )
rs.Open query, db, 3, 3, 1 ' (1) because we want to perform a query

If Not rs.EOF Then ' If the row exists

rs("UnitID") = Session.UnitID
rs("PointName1") = Session("PointName1")
rs("PointName2") = Session("PointName2")

rs.Update ' Update the row

End If

rs.Close ' Close the recordset

OnIncomingSession = True

End Function