VBA ADO查询中的OPENROWSET
问题描述:
我正在尝试在VBA程序中配置SQL UPDATE查询。VBA ADO查询中的OPENROWSET
基本上,我从一个关闭的工作簿中的表中更新当前工作簿中的一个表。这个伟大的工程使用下面的查询只,如果源工作簿是一个.xls文件:
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驱动程序”。
要做到这一点,我试图用OPENROWSET这样的:
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
不过,我得到一个“Synthax错误引用条款”。
如何正确设置我的sql查询?
感谢
答
首先,OPENROWSET
是Microsoft SQL Server的方法(即TSQL),您的链接引用。这种查询只能在SQL Server查询中运行。它不是Jet/ACE SQL引擎(您目前使用的)方法。所以你正在让你的数据库混淆起来。作为比喻,Oracle方法在Postgres数据库中不起作用。
是的,在ACE 12.0提供商可以连接到旧的的.xls和电流的.xlsx文件,只是因为它可以较旧的MS Access(办公兄弟到MS Excel)的.mdb和电流.accdb文件。只需更改版本:Excel 8.0;
至Excel 12.0 Xml;
。
事实上,你甚至不需要使用以下格式指定提供内联SQL命令:
...INNER JOIN [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\File.xlsx].[SHEETNAME$]
用的MS Access(强烈建议对数据库的需求,并提供在所有的Windows机器,无论MSACCESS.EXE应用程序的安装或不安装),你就不需要指定Excel中的参数:
...INNER JOIN [C:\Path\To\Access\File.mdb].[TABLENAME]
...INNER JOIN [C:\Path\To\Access\File.accdb].[TABLENAME]
的问题是,如果我用你的内部连接,并仍然是我的连接字符串(STRCONNECTION)与MSDQSL我得到'ISAM驱动程序未找到'。我使用此提供程序而不是ACE的真正原因是因为ACE生成以下错误:“您无法编辑此字段,因为它驻留在链接的Excel电子表格中。”从我读到的,我需要用旧MSDASQL.1替换ACE。我的连接字符串与源文件的“Excel 12.0”不兼容。如果我使用'{Microsoft Excel Driver(* .xls,* .xlsx,* .xlsm,* .xlsb)}'我也会得到链接的Excel电子表格错误。 – Lich4r
然后简单地保持原来的语法,并更改版本:'Excel 12.0 Xml;'。抱歉,我从不在工作簿上运行更新查询,也不想因为Excel不是数据库而偏离该路径。 – Parfait
顺便说一下,ACE是用于连接到工作簿的引擎,而OLEDB提供程序和ODBC驱动程序是连接到它的API。它们是互补的,而不是替代软件。ACE.12.0应连接到.xlsx文件。较旧的Jet 4.0仅限于.xls文件。 – Parfait