VBAからoracleへの接続方法

Question

テーマ、知りたいこと

VBAからノートPCへダウンロードしたoracleへ接続方法

背景、状況

ネットでoracleへの接続方法を調べてコードを書いたがエラーが出て接続できない状況です。
エラー
「プロバイダが見つかりません、正しく接続されていない可能性があります」と表示される。
コードでいうと　conn.Open　この部分でエラーが発生しています。

##試したこと
oracleクライアントのインストール、Microsoft ActiveX Data Objects X.X Libraryの参照設定。
下記のサイトの内容を参考にしました。
https://skill-note.net/vba-oracle-connect/

'TNS使用時
    Provider = "OraOLEDB.Oracle" 'Provider
    DATA_SOURCE = "XE"           'Data Source
    USER_ID = "SYSTEM"           'userid
    PASSWORD = "*********"      'password
    
    'TNSサービス名を使用せず直接接続する場合
    HOST_NAME = "LAPTOP-UA2JN1S2"     'データベースのホスト名orIPアドレス
    PORT_NO = "1521"          'データベースのポート
    SERVICE_NAME = "XE"       'サービス名
    
    'Const DATA_SOURCE = "(DESCRIPTION=(ADDRESS=(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE)))"
    SQL = "Select * from t1"
    Dim conn As New ADODB.Connection
    
    'TNSサービス名を使用せず直接接続する場合
   'cn.ConnectionString = "Provider=" & Provider & ";Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HOST_NAME)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)))" _
    '                   & ";USER ID=" & USER_ID _
     '                  & ";PASSWORD=" & Password
    
        
    'TNS使用時
    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
    
    With Worksheets("Sheet1")
    Stop
        .Cells.Clear
        
        '列名の表示
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1) = rs(i).Name
        Next i

        '値の表示
        row = 1
        Do Until rs.EOF
            For i = 0 To rs.Fields.Count - 1
                .Cells(row + 1, i + 1) = rs(i).Value
            Next i
            rs.MoveNext
            row = row + 1
        Loop
    End With
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

よろしければご教授おねがいいたします。

Comments

[KOZ6.0]

・OS は？
・Oracle クライアントのバージョンは？また、32bit/64bit のどちらですか？
・Office のバージョンは？また、32bit/64bit のどちらですか？
質問を編集して記入してください。

Answer 1

Office と Oracle のビット数違いは確定したようですね。
32ビットの Oracle クライアントをインストールしたら、コマンドプロンプトを開き、

where oci.dll

と入力してください。
２箇所ヒットすると思いますが、64ビットのものが先になっているようなら、システム環境変数の PATH を修正し、Office が 64 ビットの dll を見つけてしまわないよう 32ビットのものが先に来るようにしてください。

あと、udl ファイルを使った接続確認方法を紹介しておきます。

64ビットの場合

(1) エクスプローラーで右クリックしてテキスト文書を作成し、拡張子を udl に変更します。
(2) udl ファイルをダブルクリックすると画面が立ち上がります。
(3) プロバイダータグを選択すると、使用できるプロバイダーの一覧が表示されます。
(4) 「Oracle Provider for OLE DB」を選択して「次へ」ボタンを押します。
(5) データソース、ユーザ名、パスワードを入力し、「パスワードを保存する」チェックボックスにチェックしてください。
(6) 接続のテスト(T) を押すと、接続確認が出来ます。
(7) 接続確認が出来たら、OKボタンを押します。
(8) パスワードうんぬんのメッセージボックスが出るので、「はい」を選択

udl ファイルをメモ帳で開くと、ADO の接続文字列が記録されています。

こうして出来た UDL ファイルを使って接続することも出来ます。

udlFilePath = "udlファイル名"
Set conn = CreateObject("ADODB.Connection")
conn.Open "File Name=" & udlFilePath
MsgBox "OK"

32ビットの場合

C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile "udl ファイルのフルパス"

で画面が起動します。操作方法は 64ビットと同様です。

Answer 2

まず、利用されている Excel が 32bit / 64bit 版のどちらなのか確認ください。

プロバイダが見つかりません

ここでいう、プロバイダは OraOLEDB.Oracle のことです。
DB 接続以前に、DB 接続の為のドライバ（プロバイダ）が見つからない、という状況です。

おそらく、
・Excel が 32bit 版で、Oracle Client が 64bit 、
・Excel が 64bit 版で、Oracle Client が 32bit
のいずれかの組み合わせで実行されているのが原因ではないかと思います。

＜参考情報＞
■ Excel VBA でOracle DB接続 「プロバイダーが見つかりません。…」の対処方法
https://yuworks.blog/excel-vba-oracle-db/

Comments

[masahito11]

ご返信ありがとうございます。
Excelが32ビット版でした。添付していただいたサイトにて確認しましたが、64ビット版のインストールしか記載されておらず、自身で32ビット版を探しましたがどこにあるのか探しきれませんでした。もし可能であれば32ビット版のインストーラーがどこにあるか教えていただけると幸いです。
明日自力でなんとかするために、oracleのサイト内でチャットにて問合せしてみることも考えています。

以上ご確認お願いいたします。

[cx20]

アカウントが無くて試せてないですが、おそらく下記から落とせるのでは、と思います。

■ Oracle Data Access Components (ODAC) for Windows Downloads
https://www.oracle.com/database/technologies/odac-nuget-downloads.html

[cx20]

最新版だとこちらから落とせるようですね。
ページ下の「ODAC Xcopyパッケージ」の箇所に「64ビット」版と「32ビット」版のリンクがありました。

■ Download ODP.NET、Oracle Developer Tools for Visual Studio (Code)、およびODACのダウンロード
https://www.oracle.com/jp/database/technologies/net-downloads.html