使用ODBC连接从Excel VBA宏更新Oracle表 [英] Updating Oracle Table from Excel VBA Macro using ODBC connection

问题描述

我在Excel 2010中有一个工作表，它通过ODBC DSN从Oracle数据库中提取QueryTables中的数据。

我需要从电子表格中获取数据，使用它来更新同一个Oracle数据库中的表。我无法从QueryTable中进行更新，但这是我可以使用ODBC的唯一连接方法。

我已经尝试设置一个ADODB连接，但我得到一个

 '运行时错误'-2147467259（80004005'）：
 
自动化错误
未指定的错误
  

这是我使用的代码：

  Sub Upload_Click（）
 Dim cn As ADODB.Connection 
 Set cn = New ADODB.Connection 
 
带cn 
 .Provider =MSDASQL
 .ConnectionString =DSN = xcognosD; 
 .Open 
结束
 
 cn.Close 
 End Sub 
  

添加注释，我在Windows 7上使用64位odbc驱动程序，连接到Oracle 11数据库。

解决方案

提供者MSDASQL是 Microsoft的ODBC驱动程序的OLE DB提供程序。这是相当老了，现在已经不推荐了。它只适用于没有OLE DB提供程序的旧数据库。它也是32位的，所以它不会与64位提供程序（例如您正在尝试使用的提供程序）一起使用。你最好尝试一个OLE DB驱动程序。

用于Oracle的MS OLEDB提供程序是MSDAORA（应该预先安装在您的机器上）和 Oracle自己的OLEDB提供者是OraOLEDB.Oracle。您最好建议下载最新的Oracle提供的提供程序，因为MSDAORA也被弃用。

您需要下载并安装Oracle提供程序（如果您没有已经）

  .Provider =OraOLEDB.Oracle
  

您还需要设置 .ConnectionString 。看看 http://www.connectionstrings.com/oracle- provider-for-ole-db-oraoledb / 的一些例子。

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.

解决方案

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.

这篇关于使用ODBC连接从Excel VBA宏更新Oracle表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！