Excel ODBC Issues

[schulermx]

I'm having some issues importing external data from an Oracle 9i database.
Here is what is happening:

I am initially able to import data from the table I want to into Excel.
I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.

If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.

Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.

I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.
Ialso use this data source in Microsoft Access without any problems.

Any thoughts why I'm having these connections issues?
Thanks in advance.

 

[Blade Hunter]

I used to have the same problems when I first started pulling server based data into Excel. Rather than fix it, I just went the route of using the following VBA code to do my data import.

Sub Main()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim UID As String
Dim PWD As String
Dim Server As String
    Application.Calculation = xlCalculationManual
    UID = "USERNAME"
    PWD = "PASSWORD"
    Server = "SERVER"
    Set Data = Sheets("Main")
    Data.Select
    Cells.ClearContents
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & _
    "USER ID=" & UID & ";PASSWORD=" & PWD
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    sqlText = "SELECT STATEMENT"
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 0 To 4 ' Number of columns returning minus 1
        Data.Cells(1, X + 1) = RS.Fields(X).Name
    Next
    Do While Not RS.EOF
       Row = Row + 1
       For Findex = 0 To RS.Fields.Count - 1
         Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
       Next Findex
       RS.MoveNext
    Loop
End Sub

The conclusion I came to is that MSQuery is both inneficient and doesn't release its handle on the server thus preventing your next attempt using the handle (I could be wrong but it sounds right to me )

Hope that helps. You will need an ODBC connection named whatever you put as the server at the top of this code.

Cheers

Dan

 

[schulermx]

Hey Dan,

I'm getting a compile errors with the three declarations at the top.

"Compile Error:
User-defined type not defined."

Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command

I'm not too great at VBA. Is there some sort of library I need to import to access the ADODB objections?

Thanks,
Mike

 

[Blade Hunter]

Sorry about that, you need to go into Tools/References in the VBA Editor. You need to scroll down to Microsoft ActiveX Data Objects and tick it. If you have multiple versions, probably go the latest, I am on 2.8

 

[schulermx]

Sorry about that, you need to go into Tools/References in the VBA Editor. You need to scroll down to Microsoft ActiveX Data Objects and tick it. If you have multiple versions, probably go the latest, I am on 2.8

That fixed the compile error.

Another question, do I leave the provider as MSDAORA.Oracle or supply my own?
When I try running the script with my inputed Server, UID and PWD variables I get the following run time error.

Run-time error '-2147467259 (800040005)'
Oracle error occurred, but error message could not be retrieved from Oracle.

Clicking debug takes me to this line:

    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & _
    "USER ID=" & UID & ";PASSWORD=" & PWD

My Server, UID and PWD variables are the same as the User Name, Password and Server that I provide MS Query when trying to pull external data.

Any thoughts?
And thanks for the quick replies! They're much appreciated.

 

[Blade Hunter]

That fixed the compile error.

Another question, do I leave the provider as MSDAORA.Oracle or supply my own?
When I try running the script with my inputed Server, UID and PWD variables I get the following run time error.

Run-time error '-2147467259 (800040005)'
Oracle error occurred, but error message could not be retrieved from Oracle.

Clicking debug takes me to this line:

    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & _
    "USER ID=" & UID & ";PASSWORD=" & PWD

My Server, UID and PWD variables are the same as the User Name, Password and Server that I provide MS Query when trying to pull external data.

Any thoughts?
And thanks for the quick replies! They're much appreciated.

MSDAORA.Oracle is the Oracle one, what DB are you using?

 

[Blade Hunter]

I got this (Translated from Russian via Google) from a web search.

Driver Provider
SQLOLEDB SQL OLE DB
MSDAORA Oracle OLE DB
JOLT Jet OLE DB Provider
MSDASQL / SQLServer ODBC SQL Server ODBC Driver via OLE DB for ODBC
MSDASQL / Jet ODBC Jet ODBC Driver via OLE DB Provider for ODBC
Note: SQL managed provider supports SQL Server version 7.0 and above.
MSDASQL/Oracle ODBC DRIVER (Oracle ODBC ??????? ????? OLE ?????????? ???? ?????? ??? ODBC) ? ????????? ????? ADO.NET ?? ??????????????. MSDASQL / Oracle ODBC DRIVER (Oracle ODBC driver via OLE database provider for ODBC) currently ADO.NET is not supported.

 

[schulermx]

I got this (Translated from Russian via Google) from a web search.

Driver Provider
SQLOLEDB SQL OLE DB
MSDAORA Oracle OLE DB
JOLT Jet OLE DB Provider
MSDASQL / SQLServer ODBC SQL Server ODBC Driver via OLE DB for ODBC
MSDASQL / Jet ODBC Jet ODBC Driver via OLE DB Provider for ODBC
Note: SQL managed provider supports SQL Server version 7.0 and above.
MSDASQL/Oracle ODBC DRIVER (Oracle ODBC ??????? ????? OLE ?????????? ???? ?????? ??? ODBC) ? ????????? ????? ADO.NET ?? ??????????????. MSDASQL / Oracle ODBC DRIVER (Oracle ODBC driver via OLE database provider for ODBC) currently ADO.NET is not supported.

The drivers I believe I'm using are for Oracle with an ODBC connection. The MSDAORA is supposedly for Oracle OLE. Is your code for an OLE object?

If so, do you know if I need more Oracle drivers in order to use OLE? I currently have installed the "Instant Client" Oracle drivers in order to make my ODBC connections.

Thanks

 

[Blade Hunter]

When I create an ODBC data source in control panel for this to use, I use the Microsoft ODBC for Oracle. This was installed from the Oracle disc we have here.

 

[schulermx]

I found the solution out on another forum.
Thanks for the assistance Blade. This might be a way you can do things without your code as well.