OleDB数据提供程序找不到VBA / Excel [英] OleDB Data provider can not be found VBA/Excel
问题描述
Sub Try（）
Dim cn As New ADODB 。连接
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（尝试连接到db，Stil，Titel，Hilfe，Ktxt）
'使用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））
'这是一个例子，如果你想要向数据库发出一个直接的
'命令
'
'设置cmd =新建ADODB.Command
'使用cmd
'.CommandText =从MYTABLE删除
'.ActiveConnection = cn
'.Execute
'End With
'设置cmd = Nothing
'
'这里是如何直接插入
'数据库中的示例，而不使用
'一个记录集和AddNew方法
'
设置cmd =新建ADODB.Command
cmd.ActiveConnection = CN
'cmd.CommandText =insert into newtesttable（main，object）values（？，？）
cmd.CommandText =select * from test
cmd.CommandType = adCmdText
'主要参数
'设置main = cmd.CreateParameter（main，adInteger，adParamInput）
'main.Value = 100''随机整数值''
'cmd.Parameters.Append main
'打开文件以阅读
'fd = FreeFile
'打开myBlobFile.txt对于二进制访问读为fd
'flen = LOF（fd）
'如果flen = 0然后
'关闭
'MsgBox打开文件时出错
'结束
'结束如果
'对象参数
'
'第四个参数表示分配
'以存储对象
'Set object = cmd的内存。 CreateParameter（ 对象 ，_
'adLongVarBinary，_
'adParamInput，_
flen + 100）
'ReDim chunk（1 To flen）
'Get fd，，chunk（）
'将对象插入参数对象
'object.AppendChunk chunk（）
'cmd.Parameters.Append对象
'现在执行命令
设置rs = cmd.Execute
'Mldg =test
Stil = vbYesNo + vbCritical + vbDefaultButton1
Titel =asdasdasd
' Meldung anzeigen。
Antwort = MsgBox（rs，Stil，Titel，Hilfe，Ktxt）
'...并关闭所有
cn.Close
关闭
结束Sub
我相信这段代码有很多问题，但是在尝试执行时它失败了打开提供者找不到，可能没有正确安装。之后，我发现我需要下载并安装ORAOLEDB.dll。我这样做是通过安装ORAOledb11.dll（我已经尝试了32位和64位，你的机器是64位）。我已经通过执行 regsvr32 OraOLEDB11.dll 来安装它。
不幸的是，问题是直到那里。那么可以解决这个问题的步骤如何？
我可以以某种方式确保Oraoledb正确安装在我的机器上吗？
任何提示都将不胜感激。
32位操作系统
我设法让这个工作在 Windows XP 虚拟机通过从Oracle官方网站下载oracle OLEDB提供者 Oracle10g提供程序，用于OLE DB版本10.1.0.4.0 。
当前正在工作的链接 OLEDB用于较旧的操作系统（32 - bit）
但请注意，它会将JDK和JRE替换为较低版本（可以通过使用配置xml - products.xml - 我没有足够的精神健康药水，所以我完全安装了）。之后，您需要删除环境变量中的引用，因为它可能影响其他程序。
安装后，我注册了OraOLEDBxx.dll与 regsvc32
我正在连接到oracle db 11G与excel 2003. :)
连接字符串
我不得不启用扩展（ActiveX数据对象和记录库）。我的函数返回连接是：
公共函数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
设置conn =新建ADODB.Connection
如果provider =Oracle然后
dbConnectStr =Provider = OraOLEDB.Oracle; Data Source =&主持人：1521 /& sid& ; User Id =&用户& ;密码=& pwd& ;
Else
End If
conn.ConnectionString = dbConnectStr
conn.Open
设置connectToDb = conn
结束函数
64位操作系统，但32位Office
当我们的虚拟机迁移到具有 Excel 2010 的64位 Windows 7 时。确保您将下载 ODAC - Oracle数据访问组件 - 适用于您的excel安装的右侧 -bit 版本，因为我安装了32位excel，并认为是64位（因为Windows是64位），所以我正在生下试图让这个工作与 64位 ODAC版本。之后，我下载了 32位版本，它的工作原理与之前一样。要安装，请按照下载的归档文件夹中的安装说明进行操作。
当前工作链接
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:
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.
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?
Any tips would be greatly appreciated.
32-bit OS
I managed to get this working on Windows XP virtual machine by downloading oracle OLEDB provider from Oracle official website Oracle10g Provider for OLE DB Version 10.1.0.4.0. Current working link OLEDB for older OS (32 - bit)
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
I was connecting to oracle db 11G with excel 2003. :)
Connection string
I had to enable extensions (ActiveX Data Object and record libraries). My function returning connection was:
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
When our VMs migrated to 64-bit Windows 7 with Excel 2010. Make sure you will download ODAC - Oracle Data Access Components - for right -bit version 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-bit ODAC version. Afterwards I did download 32-bit version and it works just as before. For installation just follow install instructions included in downloaded archive folder.
Current working links for ODAC on Oracle website
这篇关于OleDB数据提供程序找不到VBA / Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
