Updating Oracle Table from Excel VBA Macro using ODBC connection

Question

I have a sheet in Excel 2010 which pulls data in QueryTables from an Oracle database, connected via an ODBC DSN.

I need to take data from the spreadsheet and use it to update a table in the same Oracle database. I cannot do an update from a QueryTable, but that is the only connection method I have been able to get to work with the ODBC.

I have tried setting up a ADODB connection, but I am getting a

'Run-time error '-2147467259 (80004005'):

Automation error
Unspecified error

Here is the code I used:

Sub Upload_Click()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "DSN=xcognosD;"
        .Open
    End With

    cn.Close
End Sub

Added note, I am on Windows 7 using a 64 bit odbc driver, connecting to Oracle 11 database.

You might find insight using the macro recorder and copying how it does the connection — enderland, Sep 16 '13 at 19:28
What action should I perform to record a macro that connects without a QueryTable? — Damienknight, Sep 16 '13 at 19:39
My Connection string follows the convention of DSN;UID;PWD;. This is a system ODBC, which has the user id and password stored in the ODBC, so all I should have to specify is the DSN. I have tried putting the UID and PWD in the Connection String, but it generated the same error. — Damienknight, Sep 17 '13 at 14:42

Monty Wild · Accepted Answer · 2013-09-19T23:49:19.927

Provider "MSDASQL" is Microsoft's OLE DB Provider for ODBC Drivers. It is quite old, and is now deprecated. It is really only for use with older databases for which there are no OLE DB providers. It is also 32-bit-Only, so it won't work with 64 bit providers (such as the one you are trying to use). You would be better off trying an OLE DB driver.

The MS OLEDB provider for Oracle is "MSDAORA" (which should be pre-installed on your machine) and Oracle's own OLEDB provider is "OraOLEDB.Oracle". You would be best advised to download the latest Oracle-provided provider, as MSDAORA is also deprecated.

You would need to download and install the Oracle provider (if you haven't already)

.Provider = "OraOLEDB.Oracle"

You would also need to set the .ConnectionString. Have a look at http://www.connectionstrings.com/oracle-provider-for-ole-db-oraoledb/ for some examples.

Damienknight · Answer 2 · 2014-09-26T13:49:27.587

Apparently the 'Run-time error '-2147467259 (80004005)' is excel's default 'There was a problem with Oracle and I dont know what it is.

First I found out that my odbc was pointing to an old DB that no longer existed. After updating the odbc, I still got the same error.

However the error no longer happened on the '.Open' portion, but on the .Execute where I was attempting to do an update. Eventually I figured out that the user did not have access to do an update to the table I was targeting.

So when you get an 'Run-time error '-2147467259 (80004005)' error, check your connections and permissions, they are likely wrong.

Here is the final code I used that worked after fixing permissions:

Sub Upload_Click1()
    Dim Oracon
    Dim recset
    Dim cmd

    Set Oracon = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Recordset")

    Oracon.Open "Data Source=xcognosD;"

    Oracon.Execute "update xstore set last_update_date = sysdate where store_cd = '0000'"

    Oracon.Close

End Sub

Updating Oracle Table from Excel VBA Macro using ODBC connection

2 Answers2

Linked