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
运行
则成功
