我对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
我相信这段代码存在许多问题，但目前执行 .Open 时失败，显示“找不到提供程序。可能未正确安装”。之后我发现需要下载并安装 ORAOLEDB.dll。我通过安装 ORAOledb11.dll（我尝试了32位和64位版本，尽管我的机器是64位）来完成这个过程。我通过执行命令“regsvr32 OraOLEDB11.dll”进行了安装。
不幸的是，问题仍然存在。 那么，如何排除此问题？ 我可以确保 Oraoledb 在我的机器上已经正确安装吗？
非常感谢您提供的任何提示。