OPENROWSET in VBA ADO query

Question

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

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

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".

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

However I get an "Synthax error in from clause".

How to properly set up my sql query ?

Thanks

Answer 1

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.

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;.

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$]

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]