Excel如何通过VBA连接oracle数据库

 1 Private Sub btn查询_Click()
 2 '请先添加引用：工具－引用－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
 3     Dim conn As New ADODB.Connection
 4     Dim rs As ADODB.Recordset
 5     Dim strConn As String
 6     Dim strconn1 As String   
 7     Set conn = CreateObject("ADODB.Connection")
 8     Set rs = CreateObject("ADODB.recordset") 
 9     strConn = "Driver={Oracle in OraClient11g_home1};Dbq=xxx;Uid=xxx;Pwd=xxx;"
10 'OraClient11g_home1为oracle安装目录，Dbq为数据库名
11     conn.Open strConn '打开数据库
12   
13 End Sub

 

strConn 为连接数据库关键语句，注意excel与oracle为相同位数，区分32位和64位

Driver={Oracle in OraClient11g_home1}使用oracle数据库自己的驱动连接oracle数据库

之前试过 strConnect = "Provider=MSDAORA.1;Password=" + strPassWD + ";Persist Security Info=True;User ID=" + strUserID + ";Data Source=" + strSource
或Provider为Provider=OraOLEDB.Oracle.1都出现了错误
错误代码为3706

 

转载于:https://www.cnblogs.com/kinglion/p/6743120.html