OLEDB Connection from Excel to Oracle Issue

Question

Everyone,

A VBA problem has been killing me the past two days. I have a Macro Based Model in Excel that has data sets bought into the spreadsheets from Oracle via OLEDB. To illustrate the problem simply I have created two functions within the Model. One using ODBC("odbc") and another using OLEDB("OraOLEDB"). The code was working completely fine last week and it has not been changed.

Now, however I get an error message that states "Run-Time Error '424': Object Required when I execute the line "conn.Open strCon" in sub "OraOLEDB".A connection can't be established with the database! So when I am trying to establish a connection to the database with that line of code, it fails. What is interesting is that via ODBC, a connection can be established. The line "conn.Open strCon" in sub "odbc" executes successfully and I am able to establish a connection to the database.

I did not change anything in the Excel Model but I did have a bunch of windows updates recently. I don't know if that corrupted anything. I think it may have. The reason why I don't want to use the ODBC connection is that it is significantly slower. I get run times 10x faster using OLEDB. Please let me know if you can help.

Sub odbc()
Dim conn As Object
Dim strCon As String

strCon = "Driver={Microsoft ODBC for Oracle}; 
          CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) 
             (HOST=xxx)(PORT=1521))
             (CONNECT_DATA=(SERVICE_NAME=xxx))); 
          uid=xxx;pwd=xxx;"

Set conn = CreateObject("ADODB.Connection")

conn.Open strCon

End Sub

Sub OraOLEDB()
Dim conn As Object
Dim strCon As String

strCon = "Provider=OraOLEDB.Oracle;
          Data Source=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)
             (HOST = xxx)(PORT = 1521)) 
             (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = xxx)));   
          User Id=xxx;Password=xxx"

Set conn = CreateObject("ADODB.Connection")

conn.Open strCon

user5683823 · Accepted Answer · 2016-04-27 12:05:19Z

2

I see the host for the OLEDB connection is modn-ast-fdb1. ... while for the ODBC connection you have modn-ast-tdb1. ... Shouldn't the host be the same?

answered Apr 27, 2016 at 12:05

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

jp3nyc Over a year ago

Thanks mathguy. Change made.

Serg · Accepted Answer · 2016-04-27 12:07:15Z

1

This means Set conn = CreateObject("ADODB.Connection") returns nothing. Check ADODB.dll registration. Alternatively you may use

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

and you'll see if ADODB is available at the moment you edit the script not waiting for runtime errors.

answered Apr 27, 2016 at 12:07

Serg

22.8k5 gold badges23 silver badges50 bronze badges

2 Comments

jp3nyc Over a year ago

Serg. Defining the variables this way fixed it. Thank you.

Rachcha Over a year ago

@jp3nyc: Please mark the answer that helped you best as accepted so that we the readers know exactly which answer solved your problem.

Collectives™ on Stack Overflow

OLEDB Connection from Excel to Oracle Issue

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related