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 執(zhí)行安裝

管理員權(quán)限執(zhí)行C:\Oracle\instantclient_12_2\odbc_install

3 增加系統(tǒng)路徑

Path增加C:\Oracle\instantclient_12_2

新增TNS_ADMIN, 路徑為C:\Oracle\instantclient_12_2

4 創(chuàng)建tnsnames.ora

在C:\Oracle\instantclient_12_2創(chuàng)建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根據(jù)具體情況修改。

5 安裝VC++ 2013? redistribution x86

如果已安裝更高版本，請卸載后安裝此版本

6 在ODBC新增數(shù)據(jù)源

進(jìn)入ODBC 數(shù)據(jù)源管理器 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

運(yùn)行

則成功