VBA ADO Update Query

Question

I am trying to create a dynamic client excel file(s) that is linked to a "server" master excel file.

The goal is to keep all data updated in each of the files. Basically, when the client file is opened I have an update from the master file, and I then want to update the master file according to every change made in the client file.

I can get data using SELECT very easily but update query won't work. Here is a part of the code :

Option Explicit


Private Type FichierSource
    'Objet Fichier source.
    Path As String
    SourceSheet As String
    TargetSheet As String
    Columns As String
    Filter As String
    Name As String
End Type

Sub GetFiles()
    'Take !M sheet to create files and their informations
    Dim Base As FichierSource

    '----------------------------
    'Create files object
    '----------------------------

    'Fichier Source
    Base.Path = "U:\Macros\SQL\Base.xlsx"
    Base.SourceSheet = "DATA"
    Base.TargetSheet = "Base2"
    Base.Columns = "*"
    Base.Filter = ""
    Base.Name = "Base.xlsx"


    '---------------------------
    'Launch queries
    '---------------------------

    With Base
        Call UPDATEQUERY(.Path, .SourceSheet, .TargetSheet, .Columns, .Filter)
    End With

End Sub

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
    Dim i As Long

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


    Set Cn = New ADODB.Connection

    QUERY_SQL = _
    "UPDATE [" & TargetSheet & "$] SET [Col] = (SELECT [Col] FROM [" & SourceSheet & "$] " & _
    "IN '" & SourcePath & "' " & CHAINE_HDR & Filter & ")"



    STRCONNECTION = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source='" & ThisWorkbook.FullName & "';" & _
    "Extended Properties=""Excel 12.0 Macro;"";"

'    QUERY_SQL = _
'    "UPDATE [" & TargetSheet & "$] SET " & _
'    "[Col] = '3'"

    'MsgBox (QUERY_SQL)
    Cn.Open STRCONNECTION

    Cn.Execute (QUERY_SQL)


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

End Sub

When I execute the commented Sql Query so as to update the column 'Col' to '3 ' it works perfectly however when I'm trying to update using the SELECT from the master file I get the following error

Operation must use an updatable query

UPDATE : I think the real problem is there :

I've read questions raised on the subject but any worked for me. Indeed If I set 'ReadOnly=False' in my connection string I get the following error 'Pilote ISAM introuvable' ('ISAM Driver not found).

UPDATE 2 : ISAM Driver error pops up whenever the connection string is not correct. (ex: a bad excel version number). The ReadOnly=False (or Mode='Share Deny Write') is needed, so is the inner join.

I've already achieved all of this manually by adding a connection to the master file in excel connection so I know this should be possible.

Thanks

Answer 1

I added the SQL tag to your question so maybe an SQL guru can help you better. However, looking at the UPDATE syntax, then an UPDATE query without a WHERE clause will update the specified column of every row of the table with the same value. Looking at your SELECT part of the query, it looks as if that will retrieve more than one value.

If you want to update the column of the table with the value of a matching column in another table, you must join the tables using a WHERE clause. I think the following would be a correct example:

UPDATE table1 SET col = (SELECT col FROM table2 WHERE table1.key=table2.key)

OR

UPDATE t1
SET t1.Col = t2.Col
FROM table1 AS t1
    INNER JOIN table2 AS t2
        ON t1.Key = t2.Key

Answer 2

I have made a similar test with an update and a join, just for fun, and it worked perfectly. Here is my code:

Sub SQLUpdateExample()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & ThisWorkbook.FullName & ";" & _
           "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"
    Set rs = New ADODB.Recordset
    Set rs = con.Execute("UPDATE [Sheet1$]  inner join [Sheet2$] on [Sheet1$].test1 = [Sheet2$].test1  SET [Sheet1$].test3 = [Sheet2$].test3 ")

    Set rs = Nothing
    Set con = Nothing
End Sub

Perhaps all you need is this ;ReadOnly=False; in your connect string ?
Note that , despite the name I use for the driver, this works in a .XLSM file.