Connection from VBA to Oracle DB

Question

With setup from text below I successfully connected and retrieve data from DB. One time, while macro was executing, my PC (windows 10) lost the power and from that moment on I'm receiving the error every time I tried to connect to DB (only while I'm trying to connect via VBA) with following message:

Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC driver for Oracle][Oracle]Error while trying to retrieve text for error ORA-01019

Error that I received is from the line with command "cn.Open (strConnection)"

I have installed ODAC 12c 32bit because I have Excel 32bit.

I think that this error is active because of info from some register which is locked after PC lost the power while retrieving data but I can't find it.
Any help or hint will be precious to me.

Thanks in advance

Configuration is:

Environment Variables:
ORACLE_HOME = C:\app\client\xxxx\product\12.2.0\client_1
PATH = C:\app\client\xxxx\product\12.2.0\client_1\Network\Admin
PATH = C:\app\client\xxxx\product\12.2.0\client_1\Network\Admin\bin

References - VBAProject:
Visual Basic for Applications
Microsoft Excel 16.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Object Recordset 2.8 Library
Microsoft OLE DB Simple Provider 1.5 Library
OraOLEDB 1.0 Type Library

Active Services:
OracleOraClient12Home1_32bitMTSRecoveryService

I tried to uninstall Oracle Client and install it again, but with no success. Also I created ORACLE_HOME variable and tried more different variants of connection string and combination of included References but error is still the same.

Dim strConneciton As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

strConneciton = "Driver={Microsoft ODBC for Oracle};" & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=xxx.xxxx.xxx)(PORT=1521)" & _
"(CONNECT_DATA=(SERVICE_NAME=xxxxx)));user id=user1;password=1234;")

cn.Open (strConneciton)

If cn.State = adStateOpen Then
 cn.Close
 MsgBox "Completed!"
Else
 MsgBox "Connection failed!"
End If

Try to add `C:\app\client\xxxx\product\12.2.0\client_1\bin` and `C:\app\client\xxxx\product\12.2.0\client_1` to your `%PATH%` — Wernfried Domscheit, Jun 16 '19 at 15:24
Try the ODBC driver from Oracle `{Oracle in OraClient12_home1}` instead of the driver from Microsoft `{Microsoft ODBC for Oracle}` - The exact name of the driver could be slightly different. Check in ODBC Administrator (the 32-bit version!) — Wernfried Domscheit, Jun 16 '19 at 15:27
https://stackoverflow.com/questions/12560925/error-while-trying-to-retrieve-text-for-error-ora-01019 — Tim Williams, Jun 16 '19 at 16:29

score 1 · Answer 1 · answered Oct 07 '21 at 20:36

I've been struggling with the same connectivity error. Here's how it worked for me.

This is an extract of my tnsnames.ora:

#VISUALTIME7 / EX PRODUCTION
VISUALTIME7_DEV=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XX.XX.XX)(PORT = XXXX))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = VISUALTIME7)
    )
  )

This is my vba code:

Sub CopyDataFromDatabase()
    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection
    Dim strCon As String
    
    'DataSource argument according to my tnsnames.ora
    strCon = "Provider=OraOLEDB.Oracle;Data Source=VISUALTIME7_DEV;User ID=myUser;Password=myPassword;"
        
    Conn.Open (strCon)
    
    Conn.Close
    
End Sub

My references in vba project:

Visual Basic For Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Microsoft ActiveX Data Objects 6.1 Library

score 0 · Accepted Answer · answered Jun 17 '19 at 18:19

Thanks for answers.
I added those locations to the PATH variable, but the problem is the same.
With change of a driver (Oracle in OraClient12Home1_32bit - which is correct name in ODBC Data Source Administrator (32bit)), I've got the following error:
TNS protocol adapter error.

I am able to connect from sqlplus to db, but the problem is when I tried to connect via VBA.

@Tim, thanks for that hint, but that is not the problem here, I've checked that before.

Finally I finished this with new Connection string as you can see below:

strConnection = "Provider=OraOLEDB.Oracle;Data Source=xxx.xxxx.xxx:1521/xxxxx;User ID=user1;Password=1234;"

Thanks again.

Connection from VBA to Oracle DB

Configuration is:

2 Answers2