1

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.

Improve this question
4
  • You might find insight using the macro recorder and copying how it does the connection Commented Sep 16, 2013 at 19:28
  • What action should I perform to record a macro that connects without a QueryTable? Commented Sep 16, 2013 at 19:39
  • connectionstrings.com/oracle Commented Sep 16, 2013 at 19:58
  • 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. Commented Sep 17, 2013 at 14:42

2 Answers 2

Reset to default
4
+25

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.

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

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.