找不到 OleDB 数据提供程序 VBA/Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14836274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
OleDB Data provider can not be found VBA/Excel
提问by Alex K.
I am almost not familiar with VBA (have had some courses back at school and that's it). Now I need to connect to Oracle database (which is running on remote server) from Excel file. I've goggled around and found some examples. So, there is the following code I have written so far:
我几乎不熟悉 VBA（在学校上过一些课程，仅此而已）。现在我需要从 Excel 文件连接到 Oracle 数据库（在远程服务器上运行）。我环顾四周，找到了一些例子。所以，到目前为止我已经编写了以下代码：
Sub Try()
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim chunk() As Byte
Dim fd As Integer
Dim flen As Long
Dim Main As ADODB.Parameter
Dim object As ADODB.Parameter
Stil = vbYesNo + vbCritical + vbDefaultButton1
Titel = "db connection test"
' Meldung anzeigen.
Antwort = MsgBox("trying to connect to db", Stil, Titel, Hilfe, Ktxt)
' Connect to the database using ODBC [msdaora][ORAOLEDB.Oracle]Provider=ORAOLEDB.Oracle;
With cn
.ConnectionString = "Provider=ORAOLEDB.Oracle;Password=pass;User ID=usr;Data Source=host:port:sid"
.Open
.CursorLocation = adUseClient
End With
ret = cn.Execute("create table newtesttable (main integer, object oid)")
' Here is an example if you want to issue a direct
' command to the database
'
'Set cmd = New ADODB.Command
'With cmd
' .CommandText = "delete from MYTABLE"
' .ActiveConnection = cn
' .Execute
'End With
'Set cmd = Nothing
'
' Here is an example of how insert directly into the
' database without using
' a recordset and the AddNew method
'
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
' cmd.CommandText = "insert into newtesttable(main,object) values(?,?)"
cmd.CommandText = "select * from test"
cmd.CommandType = adCmdText
' The main parameter
' Set main = cmd.CreateParameter("main", adInteger, adParamInput)
'main.Value = 100 '' a random integer value ''
'cmd.Parameters.Append main
' Open the file for reading
'fd = FreeFile
'Open "myBlobFile.txt" For Binary Access Read As fd
'flen = LOF(fd)
'If flen = 0 Then
' Close
' MsgBox "Error while opening the file"
' End
'End If
' The object parameter
'
' The fourth parameter indicates the memory to allocate
' to store the object
' Set object = cmd.CreateParameter("object", _
' adLongVarBinary, _
' adParamInput, _
flen + 100)
' ReDim chunk(1 To flen)
' Get fd, , chunk()
' Insert the object into the parameter object
' object.AppendChunk chunk()
' cmd.Parameters.Append object
' Now execute the command
Set rs = cmd.Execute
' Mldg = "test"
Stil = vbYesNo + vbCritical + vbDefaultButton1
Titel = "asdasdasd"
' Meldung anzeigen.
Antwort = MsgBox(rs, Stil, Titel, Hilfe, Ktxt)
' ... and close all
cn.Close
Close
End Sub
I believe there are many problems in this code, but at the moment it fails when trying to execute .Open, saying, that "Provider cannot be found. It may not be properly installed". After that I've found that I need to download and install ORAOLEDB.dll. I did this by installing ORAOledb11.dll (I've tried both 32 bit and 64 bit, thou my machine is 64 bit). I've installed it by executing regsvr32 OraOLEDB11.dll.
我相信这段代码有很多问题，但目前在尝试执行 .Open 时失败了，说，那个 "Provider cannot be found. It may not be properly installed"。之后我发现我需要下载并安装ORAOLEDB.dll。我通过安装 ORAOledb11.dll 做到了这一点（我已经尝试了 32 位和 64 位，你我的机器是 64 位的）。我已经通过执行安装了它regsvr32 OraOLEDB11.dll。
Unfortunately the problem is till there. So, what could be the steps to troubleshoot this problem? Can I somehow ensure, that Oraoledb is properly installed on my machine?
不幸的是，问题一直存在。那么，解决此问题的步骤是什么？我能以某种方式确保 Oraoledb 正确安装在我的机器上吗？
Any tips would be greatly appreciated.
任何提示将非常感谢。
回答by Marcel Gent Simonis
32-bit OS
32 位操作系统
I managed to get this working on Windows XPvirtual machine by downloading oracle OLEDB provider from Oracle official website Oracle10g Provider for OLE DB Version 10.1.0.4.0. Current working linkOLEDB for older OS (32 - bit)
我设法 通过从 Oracle 官方网站Oracle10g Provider for OLE DB Version 10.1.0.4.0下载 oracle OLEDB 提供程序，使其在Windows XP虚拟机上 运行。 旧操作系统的当前工作链接OLEDB（32 位）
But be aware it will replace JDK and JRE to lower version (It can be prevented by playing with the configuration xml - products.xml- I did not have enough of mental health potion, so I did full install instead). Afterwards you need to delete reference in environment variables as it may effect other programs. After installation, I registered OraOLEDBxx.dll with regsvc32
但请注意，它会将 JDK 和 JRE 替换为较低版本（可以通过使用配置 xml - products.xml来防止- 我没有足够的心理健康药水，所以我进行了完整安装）。之后您需要删除环境变量中的引用，因为它可能会影响其他程序。安装后，我用regsvc32注册了OraOLEDBxx.dll
I was connecting to oracle db 11G with excel 2003. :)
我正在使用 excel 2003 连接到 oracle db 11G。:)
Connection string
连接字符串
I had to enable extensions (ActiveX Data Object and record libraries). My function returning connection was:
我必须启用扩展（ActiveX 数据对象和记录库）。我的函数返回连接是：
Public Function connectToDb(provider As String, host As String, sid As String, user As String, pwd As String, db As String) As ADODB.Connection
Dim conn As ADODB.Connection
Dim dbConnectStr As String
Set conn = New ADODB.Connection
If provider = "Oracle" Then
dbConnectStr = "Provider=OraOLEDB.Oracle;Data Source=" & host & ":1521/" & sid & ";User Id=" & user & ";Password=" & pwd & ";"
Else
End If
conn.ConnectionString = dbConnectStr
conn.Open
Set connectToDb = conn
End Function
64-bit OS but 32-bit Office
64 位操作系统但 32 位办公室
When our VMs migrated to 64-bit Windows 7with Excel 2010. Make sure you will download ODAC- Oracle Data Access Components - for right -bitversion of your excel installation because I had 32-bit excel installed and thought it was 64-bit (as windows is 64-bit) so I was giving birth trying to get this working with 64-bitODAC version. Afterwards I did download 32-bit versionand it works just as before. For installation just follow install instructions included in downloaded archive folder.
当我们的 VM 迁移到带有Excel 2010 的64 位Windows 7时。确保您将下载ODAC- Oracle 数据访问组件 - 用于正确位版本的 excel 安装，因为我安装了 32 位 excel 并认为它是 64 位（因为 Windows 是 64 位）所以我正在尝试生育使其与64 位ODAC 版本一起使用。之后我确实下载了32 位版本，它和以前一样工作。对于安装，只需按照下载的存档文件夹中包含的安装说明进行操作。
Current working linksfor ODAC on Oracle website
Oracle 网站上 ODAC 的当前工作链接
回答by jbay
You can insure that your connection string is accurate by creating a text file with the ending .udlthen close and open the file. You'll be prompted with a user interface to connect to the server. Enter your info and test the connection. Then if your connection is working close the file. Open that file in text format and copy the connection string into your code. Also be sure that your reference library is selected for ADO. This line looks wrong:
您可以通过创建一个以.udl结尾的文本文件，然后关闭并打开该文件来确保您的连接字符串是准确的。系统会提示您连接到服务器的用户界面。输入您的信息并测试连接。然后，如果您的连接正常，请关闭该文件。以文本格式打开该文件并将连接字符串复制到您的代码中。还要确保为 ADO 选择了您的参考库。这一行看起来不对：
Data Source=host:port:sid
Below is a sample I use to pull in the sql from text and extract results to text.
下面是我用来从文本中提取 sql 并将结果提取到文本的示例。
Public Function ObjectConnect(AID As String, APswd As String)
ObjectConnect = "Provider=ORAOLEDB;Password=" & APswd & ";Persist Security Info=True;User ID=" & AID & ";Data Source=(nameofserverConn)"
End Function
Sub RunSQL()
Dim strConn As String
Dim Query As String
Dim txt As Object
Dim ns As ADODB.Connection
Dim rs As ADODB.Recordset
Dim txtfile As Object
Dim f As ADODB.Field
Dim myFileSystemObject As Object
Dim txtN As String
Dim DL As String
FName1 = ""
Query = ""
txtStrngX = ""
Set ns = New ADODB.Connection
Set rs = New ADODB.Recordset
ns.ConnectionTimeout = 99000
ns.CommandTimeout = 99000
ns.Open ObjectConnect('UserID', 'Password') 'this is a public function w. userform for people to enter ID and Password.
With rs
.ActiveConnection = ns
'however you're writing the sql it would go here.
.Open Query
End With
If rs.State <> 0 Then
DL = Sheet1.Cells(2, 2)
RecordsetToText rs:=rs, FullPath:=txtN, ValueDelimiter:=DL 'this is a sub function that writes to a text file. Didn't include the code but left this. Here you would want to do something with the recordset.
End If
On Error Resume Next
rs.Close
Set rs = Nothing
End Sub
