ORA-01019 connecting to Oracle from Excel

I have installed Oracle 10g Express Edition. When try to test the connection I am getting the error "Error while trying to retrieve text for error ORA-01019".

Below is my code.

  strConnection = "Driver={Microsoft ODBC for 
  Oracle};Ser开发者_开发百科ver=Servername;Uid=username;Pwd=password;"    
  Set conn = CreateObject("ADODB.Connection")
  conn.Open strConnection

  conn.Close
  Set conn = Nothing

Thanks in advance

---

I had the following error occur recently.

System.Runtime.InteropServices.COMException (0x80004005): ORA-01019: unable to allocate memory in the user side
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)

I managed to resolve the problem by simply modifying my connection string.

from:

 "Provider=MSDAORA.1;Data Source=tprss;Persist Security Info=True;User ID=myUser;Password=myPassword"

To:

   "Provider=MSDASQL;Data Source=tprss;Persist Security Info=True;User ID=myUser;Password=myPassword"

someone modified/updated the components on the box.

---

"ORA-01019 unable to allocate memory in the user side

Cause: The user side memory allocator returned an error.

Action: Increase the size of the process heap or switch to the old set of calls."

Followup from the comments:

Could you try this code?

Dim Cn As ADODB.Connection
Dim CP As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String

'Connect to Oracele server begin
Conn = "DRIVER={ORACLE ODBC DRIVER};SERVER=Service name;UID=username;PWD=password;DBQ=Service name;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"

Set Cn = New ADODB.Connection

With Cn
     .ConnectionString = Conn
     .CursorLocation = adUseClient
     .Open
End With

If Cn.State = adStateOpen Then
    MsgBox "Connection successful."
End If

'Connect to Oracle server end


'close connection begin

Cn.Close
Set Cn = Nothing
Set CP = Nothing

'close connection end

---

(This didnt fit in a Comment box)

You need at least one driver. The oracle driver is best but Microsoft Driver will work too.

Lets first try to make a connection string. Right click on your desktop and the create a new .txt file. Now rename your textfile to something.udl Double click on the udl file. Go to "Provider" and select Microsoft OLEDB Provider for Oracle. Then click on next. In the server name field you fill in your TNS name. Then username and password and put a V inside "Allow saving password" (we will need this) And click on test connection. Make sure this works.

If it works then click on OK. Now open the UDL file with a text editor. You will see something similar to:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS;Persist Security Info=True

Copy this part into your connection string:

Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS

Now your connection string should look like:

Conn = "Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS"

I hope this works.

---

We had the same problem, specifically on Windows 7 when using the Microsoft OleDb driver from VB6. Following the instructions in this post fixed out problem: http://prasanth4microsoft.blogspot.com/2010/11/windows7-excel-vba-ora-01019-unable-to.html

---

I had this problem also but it is on win10.. After I have tried a lots of different solution from web .. Finally.. it worked to change connection string to fix this problem.. But I changed "Provider=MSDAORA.1" to "Provider=OraOLEDB.Oracle"