OleDB data provider not found. VBA / Excel

Question

I am almost unfamiliar with VBA (I had courses at school and it was). Now I need to connect to the Oracle database (which runs on a remote server) from an Excel file. I went around and found some examples. So, there is the following code that 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 that there are many problems in this code, but at the moment this fails when trying to execute. Open by saying that "Provider cannot be found. It may not be properly installed" . "Provider cannot be found. It may not be properly installed" After that, I found that I needed to download and install ORAOLEDB.dll. I did this by installing ORAOledb11.dll (I tried both 32-bit and 64-bit, my computer is 64 bit). I installed it by running regsvr32 OraOLEDB11.dll .

Unfortunately, the problem is before that. So what can be the steps to fix this problem? Can I somehow make sure that Oraoledb is installed correctly on my machine?

Any advice is appreciated.

Answer 1

32-bit OS

I managed to get this work to work with the Windows XP virtual machine by downloading the oracle OLEDB provider from the Oracle Oracle10g Provider official site for OLE DB version 10.1.0.4.0 . Ongoing OLEDB link for older OS (32-bit)

But keep in mind that it will replace JDK and JRE with a lower version (it can be prevented by playing with the xml configuration - products.xml . I didn’t have enough potion for mental health, so I did a full installation instead). After that, you need to remove the link in the environment variables, as this may affect other programs. After installation, I registered OraOLEDBxx.dll with regsvc32

I connected to oracle db 11G with excel 2003. :)

Connection string

I had to activate extensions (ActiveX Data Object and record libraries). My function returning the 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 version of Office

When our virtual machines switched to 64-bit Windows 7 with Excel 2010 . Make sure you download ODAC - Oracle Data Access Components - for the right -bit version of your excel installation, because I had 32-bit excel, and I thought it was 64-bit (since Windows is 64-bit), so I gave birth trying to make this work with the 64-bit ODAC version. Subsequently, I downloaded the 32-bit version and works the same way as before. To install, simply follow the installation instructions included in the downloaded archive folder.

Ongoing Link Operation for ODAC on Oracle Website

Answer 2

You can ensure that your connection string is correct by creating a text file ending with .udl, then close and open the file. You will be offered a user interface to connect to the server. Enter the information and check the connection. Then, if your connection is working, close the file. Open this file in text format and copy the connection string into your code. Also make sure your reference library is selected for ADO. This line looks wrong:

 Data Source=host:port:sid 

Below is an example that I use to pull sql from text and extract the results into text.

  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