vbaでoracle接続のクラス

vbaOracle接続のクラスを作成してみた。

Option Explicit

Const PROVIDER = "OraOLEDB.Oracle"
Const DATA_SOURCE = "XEPDB1"
Const USER_ID = "OT"
Const PASSWORD = "OT"
Private conn As ADODB.Connection

Public Sub dbOpen()
    conn.Open
End Sub

Public Sub executeSql(sql As String)
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.Execute
End Sub

Public Function selectSql(sql As String) As ADODB.Recordset
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = sql
    
    Dim rs As ADODB.Recordset
    Set rs = cmd.Execute
    
    Set selectSql = rs
End Function

Public Sub dbClose()
    conn.Close
End Sub

Private Sub Class_Initialize()
    Set conn = New ADODB.Connection
    
    conn.ConnectionString = "Provider=" & PROVIDER & ";" & _
        "Data Source=" & DATA_SOURCE & ";" & _
        "USER ID=" & USER_ID & ";" & _
        "Password=" & PASSWORD & ";"
End Sub

vbaでOracleに接続

 

Const PROVIDER = "OraOLEDB.Oracle"
Const DATA_SOURCE = "XEPDB1"
Const USER_ID = "USER"
Const PASSWORD = "PASS"

Sub Test()
Dim sql As String
sql = "select sysdate from dual"
Dim conn As New ADODB.Connection

conn.ConnectionString = "Provider=" & PROVIDER & ";" & _
"Data Source=" & DATA_SOURCE & ";" & _
"USER ID=" & USER_ID & ";" & _
"Password=" & PASSWORD & ";"

conn.Open

Dim rs As New ADODB.Recordset

rs.Source = sql
rs.ActiveConnection = conn

rs.Open


Do Until rs.EOF
MsgBox (rs(0).Value)

rs.MoveNext
Loop

rs.Close

conn.Close

MsgBox "test"
End Sub