VBA ADO查询中的OPENROWSET [英] OPENROWSET in VBA ADO query

问题描述

我正在尝试在VBA程序中配置SQL UPDATE查询。

I am trying to configure an SQL UPDATE query in a VBA program.

基本上，我从封闭工作簿中的表更新当前工作簿中的表。仅当源工作簿是.xls文件时，使用以下查询才能很好地工作：

Basically, I update a table in the current workbook from a table in a closed workbook. This works great using the below query only if the source workbook is a .xls file :

Sub UPDATEQUERY(SourcePath As String, SourceSheet As String, TargetSheet As String, _
Columns As String, Filter As String)

    Dim Cn As ADODB.Connection
    Dim QUERY_SQL As String
    Dim CHAINE_HDR As String
    Dim STRCONNECTION As String

    CHAINE_HDR = "[Excel 8.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=1;Extended Properties='HDR=YES;'] "

    Set Cn = New ADODB.Connection

    QUERY_SQL = _
    "UPDATE [" & TargetSheet & "$] INNER JOIN (SELECT * FROM [" & SourceSheet & "$] " & _
    "IN '" & SourcePath & "' " & CHAINE_HDR & ") t2 " & _
    "ON [" & TargetSheet & "$].id = t2.id " & _
    "SET [" & TargetSheet & "$].ColA = t2.ColA "

    STRCONNECTION = _
    "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DriverId=790;" & _
    "Dbq=" & ThisWorkbook.FullName & ";" & _
    "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"


    Cn.Open STRCONNECTION
    Cn.Execute (QUERY_SQL)

    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing

End Sub

使用.xlsx文件作为源文件，我想使用与连接到当前wb相同的提供程序/驱动程序连接到源，即MSDASQL.1而不是Microsoft.ACE.OLEDB.12.0。的确，如果我仅将 CHAINE_HDR设置为 Excel 12.0 ，则会得到未找到ISAM驱动程序。

So as to use .xlsx file as the source file I want to connect to the source using the same provider/driver that I use to connect to the current wb, that is MSDASQL.1 instead of Microsoft.ACE.OLEDB.12.0. Indeed If I only set the 'CHAINE_HDR' to Excel 12.0 I get a "ISAM driver not found".

要实现此目的，我尝试使用 OPENROWSET 像这样：

To achieve this I'm trying to use OPENROWSET like this :

Sub UPDATEQUERY(SourcePath As String, SourceSheet As String, TargetSheet As String, _
Columns As String, Filter As String)

    Dim Cn As ADODB.Connection
    Dim QUERY_SQL As String
    Dim STRCONNECTION As String
    Dim STRCONNECTION_SOURCE As String


    STRCONNECTION_SOURCE = _
    "'MSDASQL.1'," & _
    "'Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & SourcePath & ";'," & _
    "'SELECT * FROM [Data$]'"

    Set Cn = New ADODB.Connection

    QUERY_SQL = _
    "UPDATE [" & TargetSheet & "$] INNER JOIN (SELECT * FROM OPENROWSET(" & STRCONNECTION_SOURCE & ")) t2 " & _
    "ON [" & TargetSheet & "$].id = t2.id " & _
    "SET [" & TargetSheet & "$].ColA = t2.ColA "

    STRCONNECTION = _
    "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DriverId=790;" & _
    "Dbq=" & ThisWorkbook.FullName & ";" & _
    "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"


    Cn.Open STRCONNECTION
    Cn.Execute (QUERY_SQL)

    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing

End Sub

但是我得到了 from子句中的Synthax错误 。

如何正确设置我的sql查询？

谢谢

推荐答案

首先， OPENROWSET 是您的链接引用的Microsoft SQL Server方法（即TSQL）。这样的查询只能在SQL Server查询中运行。它不是Jet / ACE SQL引擎（您当前正在使用）方法。因此，您正在混淆数据库。类似地，Oracle方法在Postgres数据库中不起作用。

First, OPENROWSET is a Microsoft SQL Server method (i.e., TSQL) which your link references. Such queries would only run inside an SQL Server query. It is not a Jet/ACE SQL Engine (which you are currently using) method. So you are getting your databases mixed up. As analogy, Oracle methods would not work in Postgres databases.

是的，ACE 12.0提供程序可以连接到较旧的 .xls 和最新版本。 .xlsx 文件，就像使用旧版MS Access（Office到MS Excel的同级文件） .mdb 和当前 .accdb 文件一样。只需更改版本： Excel 8.0; 到 Excel 12.0 Xml; 。

And yes, the ACE 12.0 provider can connect to both older .xls and current .xlsx files, just as it can with older MS Access (Office sibling to MS Excel) .mdb and current .accdb files. Simply change the versions: Excel 8.0; to Excel 12.0 Xml;.

实际上，您甚至不需要使用以下格式在嵌入式SQL命令中指定 Provider ：

In fact, you do not even need to specify the Provider in the inline SQL commands by using the following format:

...INNER JOIN [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\File.xlsx].[SHEETNAME$]

具有MS Access（强烈建议满足数据库需求，并且可以在所有Windows计算机上使用（无论是否安装了MSAccess.exe应用），您都无需指定Excel参数：

With MS Access (highly advised for database needs, and available on all Windows machines, regardless of MSAccess.exe app install or not), you would not need to specify Excel parameters:

...INNER JOIN [C:\Path\To\Access\File.mdb].[TABLENAME]

...INNER JOIN [C:\Path\To\Access\File.accdb].[TABLENAME]

这篇关于VBA ADO查询中的OPENROWSET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！