Public Property Let Username(sUsername As String)
Public Property Get Username() As String
Public Property Let Password(sPassword As String)
Public Property Get Password() As String
Public Property Let ConnectionName(sConnectionName As String)
Public Property Get ConnectionName() As String

The Username and Password properties define the username and password to use when accessing the databases on the Oracle server. They default to null. ConnectionName is the ODBC data source name that was defined in the 32bit ODBC control panel object. There are ways to create data sources programmatically, but we are not going to cover that in this issue.

Public Function Connect() As Boolean
Public Sub Disconnect()

Connect is the method that will connect to the remote data source. If it returns True everything went okay. If it raises an error or returns False there was a problem connecting to the data source. Disconnect will disconnect from the remote data source.

Public Property Set Workspace(oWorkspace As Workspace)
Public Property Get Workspace() As Workspace

The Workspace property defines the reference to the Workspace object that was created to manage the database connections. This is not valid until the Connect method is called.

Public Property Set Connection(oConnection As Connection)
Public Property Get Connection() As Connection

The Connection property defines the connection that was made when the Oracle database was connected to with the Connect method.

Public Sub OpenRecordset(sSQL As String, dbType As RecordsetTypeEnum)
Public Property Get Recordset() As Recordset
Public Sub CloseRecordset()

This sample opens the Business data source with the username ALLOC and the password ALLOC. It then opens a recordset looking for all the records in the Inventory table that have a price greater than 100.

    Dim Oracle As New OracleController
    Dim rsSrc As Recordset

    Oracle.Username = "ALLOC"
    Oracle.Password = "ALLOC"
    Oracle.ConnectionName = "Business"
    Oracle.Connect
    Oracle.OpenRecordset "select * from inventory WHERE price > 100.00", dbOpenSnapshot
    Set rsSrc = Oracle.Recordset
    Debug.Print "List of items with a price greater than 100."
    While Not rsSrc.EOF
        Debug.Print rsSrc.Fields("InvNum"), rsSrc.Fields("Description")
        rsSrc.MoveNext
    Wend
    Oracle.CloseRecordset
    Oracle.Disconnect
    Set Oracle = Nothing

Just create a new class module and then insert this code. Rename the class module to cOracleController.

'----------------------------------------------------------------------
'
'   Module Name:    cOracleController
'   Written By:     C&D Programming Corp.
'   Create Date:    12/98
'   Copyright:      Copyright 1999 by C&D Programming Corp.  Source
'                   code may not be reproduced except for use in a
'                   compiled executable.  All rights reserved.  If
'                   you would like to reprint any or all of this
'                   code please email us at info@codeoftheweek.com
'----------------------------------------------------------------------
Option Explicit

Dim moWorkspace As Workspace
Dim moConnection As Connection
Dim moRecordSet As Recordset

Dim msUsername As String
Dim msPassword As String
Dim msWorkspaceName As String
Dim msConnectionName As String

Public Property Set Workspace(oWorkspace As Workspace)
    Set moWorkspace = oWorkspace
End Property

Public Property Get Workspace() As Workspace
    Set Workspace = moWorkspace
End Property

Public Property Set Connection(oConnection As Connection)
    Set moConnection = oConnection
End Property

Public Property Get Connection() As Connection
    Set Connection = moConnection
End Property

Public Property Let Username(sUsername As String)
    msUsername = sUsername
End Property

Public Property Get Username() As String
    Username = msUsername
End Property

Public Property Let Password(sPassword As String)
    msPassword = sPassword
End Property

Public Property Get Password() As String
    Password = msPassword
End Property

Public Property Let WorkspaceName(sWorkspaceName As String)
    msWorkspaceName = sWorkspaceName
End Property

Public Property Get WorkspaceName() As String
    WorkspaceName = msWorkspaceName
End Property

Public Property Let ConnectionName(sConnectionName As String)
    msConnectionName = sConnectionName
End Property

Public Property Get ConnectionName() As String
    ConnectionName = msConnectionName
End Property

Public Function Connect() As Boolean
    On Error GoTo Handler
    Set Workspace = DBEngine.CreateWorkspace(WorkspaceName, Username, Password, dbUseODBC)
    Set Connection = Workspace.OpenConnection(ConnectionName)
    Connect = True
    Exit Function

Handler:
    Set Workspace = Nothing
    Set Connection = Nothing
    Connect = False
    Err.Raise Err.Number, "OracleController.Connect", Err.Description
End Function

Public Sub Disconnect()
    Connection.Close
    Workspace.Close
    Set Workspace = Nothing
    Set Connection = Nothing
End Sub

Private Sub Class_Initialize()
    WorkspaceName = "OracleWorkspace"
End Sub

Public Sub OpenRecordset(sSQL As String, dbType As RecordsetTypeEnum)
    Connection.QueryTimeout = 0
    Set moRecordSet = Connection.OpenRecordset(sSQL, dbType)
End Sub

Public Property Get Recordset() As Recordset
    Set Recordset = moRecordSet
End Property

Public Sub CloseRecordset()
    If Not moRecordSet Is Nothing Then
        moRecordSet.Close
        Set moRecordSet = Nothing
    End If
End Sub