从vba连接到oracle数据源的问题 [英] Issues in connecting to oracle data source from vba

问题描述

 我使用以下代码连接并从oracle数据源获取详细信息，但最终出现了一些错误。我的代码是 

 I have used the following code to connect and fetch details from oracle data source and ended up with some errors.My code is 

strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
                   "CONNECTSTRING=(DESCRIPTION=" & _
                   "(ADDRESS=(PROTOCOL=TCP)" & _
                   "(HOST=" & strHost & ")(PORT=1523))" & _
                   "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"

    Set oConOracle = CreateObject("ADODB.Connection")
    Set oRsOracle = CreateObject("ADODB.Recordset")
    oConOracle.Open (strConOracle)

我遇到了这个 "运行时错误'-2147467259（80004005）'："执行connection.open语句时出错。你能和我们分享一个解决方案吗？

I faced this  " Run-time error '-2147467259 (80004005)': " error on executing the connection.open statement. Could you guys please share me a solution to come over this 

提前致谢

Ashok

推荐答案

是否要通过ODBC或ADODB连接到Oracle？

Are you want to connect to Oracle via ODBC or ADODB?

你安装吗？司机客户？

假设是 - ODBC样本

Let say that yes - ODBC sample

Const ODBC

=" Provider = MSDASQL.1; Persist Security Info = True; User ID = NAME_USR; Data Source = NAME_ODBC; Password = PASS" ;
Dim c As New ADODB.Connection
Dim d As New ADODB.Recordset
Dim sqlString

= "Provider=MSDASQL.1;Persist Security Info=True;User ID=NAME_USR;Data Source=NAME_ODBC;Password=PASS" Dim c As New ADODB.Connection Dim d As New ADODB.Recordset Dim sqlString

sub DataIntoExcel（）
'MVP OShon来自VBATools.pl

sqlString =" select * from database"

如果c.state<> 1然后c。打开ODBC
如果d.state = 1那么d.Close
d.Open sqlString，c，adOpenStatic，adLockReadOnly

On Error GoTo err_
Application.ScreenUpdating = False
Dim Y&，z&，q&
Y = 2
对于q = 1到d.Fields.Count
单元格（1，q）= d.Fields.item（q - 1）.name
下一个q
而不是d.EOF
对于z = 1到d.Fields.Count
单元格（Y，z）.value = d.Fields（z - 1）
下一个z
Y = Y + 1
d.MoveNext
Wend
Application.ScreenUpdating = True
done：
on error resume next
d。关闭
c.Close
exit sub
err_：
debug.print sqlString
MsgBox Err.Number& vbCr _
& Err.Description，vbExclamation，"VBATools.pl"
简历完成
end sub

sub DataIntoExcel() 'MVP OShon from VBATools.pl sqlString = "select * from database" If c.state <> 1 Then c.Open ODBC If d.state = 1 Then d.Close d.Open sqlString , c, adOpenStatic, adLockReadOnly On Error GoTo err_ Application.ScreenUpdating = False Dim Y&, z&, q& Y = 2 For q = 1 To d.Fields.Count Cells(1, q) = d.Fields.item(q - 1).name Next q While Not d.EOF For z = 1 To d.Fields.Count Cells(Y, z).value = d.Fields(z - 1) Next z Y = Y + 1 d.MoveNext Wend Application.ScreenUpdating = True done: on error resume next d.Close c.Close exit sub err_: debug.print sqlString MsgBox Err.Number & vbCr _ & Err.Description, vbExclamation, "VBATools.pl" resume done end sub

不要忘记添加到参考： Microsoft ActiveX Data Objects 2，x Library

Do not forget add into reference: Microsoft ActiveX Data Objects 2,x Library

这篇关于从vba连接到oracle数据源的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！