ORA-01019 connecting to Oracle from Excel

Question

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};Server=Servername;Uid=username;Pwd=password;"    
  Set conn = CreateObject("ADODB.Connection")
  conn.Open strConnection

  conn.Close
  Set conn = Nothing

Thanks in advance

Answer 1

"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

Answer 2

(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.