Vba Ado Update Query

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

Solution 1:

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 = NothingSet con = NothingEndSub

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.

Solution 2:

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