Excel VBA通过ODBC instant client连接

1  下载database instantclient 12.2   32位

http://www.oracle.com/technetwork/topics/winsoft-085727.html

instantclient-basic-nt-12.2.0.1.0.zip

instantclient-odbc-nt-12.2.0.1.0-2.zip

下载base和ODBC，解压到一个文件夹，然后放到C:\Oracle\instantclient_12_2

2 执行安装

管理员权限执行C:\Oracle\instantclient_12_2\odbc_install

3 增加系统路径

Path增加C:\Oracle\instantclient_12_2

新增TNS_ADMIN, 路径为C:\Oracle\instantclient_12_2

4 创建tnsnames.ora

在C:\Oracle\instantclient_12_2创建tnsnames.ora文件。格式如下：

XXX=

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST =1.1.22.2)(PORT =1521))

    )

   (CONNECT_DATA =

     (SERVICE_NAME =orcl)

    )

  )

注意：HOST,PORT,SERVICE_NAME根据具体情况修改。

5 安装VC++ 2013  redistribution x86

如果已安装更高版本，请卸载后安装此版本

6 在ODBC新增数据源

进入ODBC 数据源管理器 32位

7 开启Excel

添加引用

输入代码

Public Sub ConOra()

'   On Error GoTo ErrMsg:

   Dim ConnDB As ADODB.Connection

   Set ConnDB = New ADODB.Connection

   Dim ConnStr As String

   Dim DBRst As ADODB.Recordset

   Set DBRst = New ADODB.Recordset

   Dim SQLRst As String

   Dim OraOpen As Boolean

   OraOpen = False

    OraID= "BIDBPRD1"

   OraUsr = "deesiondw"

   OraPwd = "your password"

   ConnStr = "Driver={Oracle in instantclient_12_2};Password="& OraPwd & ";User ID=" & OraUsr & ";DataSource=" & OraID & ";Persist Security Info=True"

   ConnDB.CursorLocation = adUseServer

   ConnDB.Open ConnStr

   OraOpen = True 

   MsgBox "Connect to the oracle database Successful!",vbInformation, "Connect Successful"

   DBRst.ActiveConnection = ConnDB

   DBRst.CursorLocation = adUseServer

   DBRst.LockType = adLockBatchOptimistic

   SQLRst = "Select sysdate From dual"

   DBRst.Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic

   DBRst.MoveFirst

   Exit Sub

ErrMsg:

   OraOpen = False

   MsgBox "Connect to the oracle database fail ,please check!",vbCritical, "Connect fail!"

End Sub

运行

则成功