データベース（Oracle）に接続する（OLE DB を使用）

2017/09/27 2017/09/28

OLE DB プロバイダ

OLE DBには、Oracle 提供の Oracle Provider for OLE DB と Microsoft 提供の OLE DB Provider for Oracle の２種類があります。いずれの場合も ADO を使用してプログラムが可能です。
また、OLE DB の他に、ODBC や oo4o といったミドルウェアがあります。

Oracle Provider for OLE DB

Oracle に接続するには、Oracle Client が必要です。
ADO で接続するには Open メソッドの ConnectionString プロパティ に、OraOLEDB.Oracle という文字列を使用します。

接続文字列パラメータ

【ConnectionString（ADO）に設定する文字列】

＜tnsnames.ora ファイルを使用する場合＞
Provider = OraOLEDB.Oracle;
Data Source = ServerName;
User ID = UserName;
Password = UserPassword;

＜tnsnames.ora ファイルを使用せず直接指定する場合＞
Provider = OraOLEDB.Oracle;
Data Source = StrDataSource;
User ID = UserName;
Password = UserPassword;

引数・戻り値

ServerName ・・・ tnsnames.ora ファイルのネットサービス名を指定します。
UserName ・・・ユーザー名を指定します。
UserPassword ・・・パスワードを指定します。
StrDataSource ・・・ tnsnames.ora ファイルの内容をそのまま記述します。

＜tnsnames.ora ファイル＞

ADO に関してはデータベース（Access 等）に接続（ADO）を参照して下さい。

データベース（Oracle）へ接続する例（Oracle OLE DB を使用）

サンプルVBAソース1

Sub Sample_OLEDB_Oracle()

    'tnsnames.ora ファイルのネットサービス名'
    Const STRDATASOURCE = "hpdb1"
    
    'tnsnames.ora を使用しない場合（直接指定）
    'Const STRDATASOURCE = _
    '    "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)" & _
    '    "(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SID = hpdb1)))"
    
    Const USERNAME = "uhpdb1"       '接続するデータベースのユーザー名
    Const PASSWORD = "uhpdb1"       'パスワード
    
    Dim oraCon As New ADODB.Connection
    Dim oraCmd As New ADODB.Command
    Dim constr As String
    Dim strSQL As String

    constr = "Provider=OraOLEDB.Oracle"
    constr = constr & ";Data Source=" & STRDATASOURCE
    constr = constr & ";User ID=" & USERNAME
    constr = constr & ";Password=" & PASSWORD
    
    oraCon.ConnectionString = constr
    oraCon.Open
    
    strSQL = "insert all "
    strSQL = strSQL & "into table01 (id, name) "
    strSQL = strSQL & "values (1, '山田太郎') "
    strSQL = strSQL & "into table01 (id, name) "
    strSQL = strSQL & "values (2, '山本花子') "
    strSQL = strSQL & "into table01 (id, name) "
    strSQL = strSQL & "values (3, '田中一郎') "
    strSQL = strSQL & "select * from dual"

    With oraCmd
        .ActiveConnection = oraCon
        .CommandText = strSQL
        .Execute
    End With

    Set oraCmd = Nothing
    oraCon.Close
    Set oraCon = Nothing

End Sub

Sub Sample_OLEDB_Oracle()

'tnsnames.ora ファイルのネットサービス名'

Const STRDATASOURCE = "hpdb1"

'tnsnames.ora を使用しない場合（直接指定）

'Const STRDATASOURCE = _

' "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)" & _

' "(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SID = hpdb1)))"

Const USERNAME = "uhpdb1" '接続するデータベースのユーザー名

Const PASSWORD = "uhpdb1" 'パスワード

Dim oraCon As New ADODB.Connection

Dim oraCmd As New ADODB.Command

Dim constr As String

Dim strSQL As String

constr = "Provider=OraOLEDB.Oracle"

constr = constr & ";Data Source=" & STRDATASOURCE

constr = constr & ";User ID=" & USERNAME

constr = constr & ";Password=" & PASSWORD

oraCon.ConnectionString = constr

oraCon.Open

strSQL = "insert all "

strSQL = strSQL & "into table01 (id, name) "

strSQL = strSQL & "values (1, '山田太郎') "

strSQL = strSQL & "into table01 (id, name) "

strSQL = strSQL & "values (2, '山本花子') "

strSQL = strSQL & "into table01 (id, name) "

strSQL = strSQL & "values (3, '田中一郎') "

strSQL = strSQL & "select * from dual"

With oraCmd

.ActiveConnection = oraCon

.CommandText = strSQL

.Execute

End With

Set oraCmd = Nothing

oraCon.Close

Set oraCon = Nothing

End Sub

Microsoft OLE DB Provider for ODBC

Oracle に接続するには、Oracle Client が必要です。
ADO で接続するには Open メソッドの ConnectionString プロパティ に、MSDAORA という文字列を使用します。

接続文字列パラメータ

【ConnectionString（ADO）に設定する文字列】

＜tnsnames.ora ファイルを使用する場合＞
Provider = MSDAORA;
Data Source = ServerName;
User ID = UserName;
Password = UserPassword;

＜tnsnames.ora ファイルを使用せず直接指定する場合＞
Provider = MSDAORA;
Data Source = StrDataSource;
User ID = UserName;
Password = UserPassword;

引数・戻り値

ServerName ・・・ tnsnames.ora ファイルのネットサービス名を指定します。
UserName ・・・ユーザー名を指定します。
UserPassword ・・・パスワードを指定します。
StrDataSource ・・・ tnsnames.ora ファイルの内容をそのまま記述します。

データベース（Oracle）へ接続する例（Microsoft OLE DB を使用）

サンプルVBAソース 2

　
Sub Sample_OLEDB_Microsoft()

    'tnsnames.ora ファイルのネットサービス名'
    'Const STRDATASOURCE = "hpdb1"
    
    'tnsnames.ora を使用しない場合（直接指定）
    Const STRDATASOURCE = _
        "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)" & _
        "(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SID = hpdb1)))"
    
    Const USERNAME = "uhpdb1"       '接続するデータベースのユーザー名
    Const PASSWORD = "uhpdb1"       'パスワード
    
    Dim oraCon As new ADODB.Connection
    Dim oraRs As new ADODB.Recordset
    Dim constr As String
    Dim strSQL As String

    constr = "Provider=MSDAORA"
    constr = constr & ";Data Source=" & STRDATASOURCE
    constr = constr & ";User ID=" & USERNAME
    constr = constr & ";Password=" & PASSWORD
    
    oraCon.ConnectionString = constr
    oraCon.Open
    
    strSQL = "select * from table01"

    oraRs.Open strSQL, oraCon
    
    With Worksheets("oracle")
        .Cells.Clear
        .Range("A1").CopyFromRecordset oraRs
    End With
    
    oraRs.Close
    oraCon.Close
    Set oraRs = Nothing
    Set oraCon = Nothing

End Sub