Source code for Issue Number 68

Copyright 1997-2000 by C&D Programming Corp. All Rights Reserved. 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

Code of the Week Home

Source Code

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