sql - UPDATE Table in Access taking data from Excel Range

Question

I'm trying to update a table in Access, taking data from a range(rng) in Excel. I adapted a macro from Brad Importing Excel worksheet range to Ms Access Table but I'm receiving the Run-time error '3219'. If I substitute sqlTransferFromExcel = "UPDATE TableA T1 " with "Select FROM TableA T1 " it works. The issue I suppose is liked to the UPDATE statment. Here the code:

    Public Sub ImportFromWorksheet()
        Dim strFile As String, strCon As String, strMsg
        Dim sht As Worksheet
        Dim TipoRecordset As RecordsetTypeEnum
        Dim db_Open As Boolean, rs_Open As Boolean
        Dim rs As DAO.Recordset
        Dim nRows As Integer
        Dim getRecordset()
        
        Set sht = ActiveSheet
        strFile = sht.Parent.FullName
        strCon = "Excel 12.0;HDR=No;Database=" & strFile
    
        Dim strSql As String, sqlTransferFromExcel As String
    
        Dim row As Long
        row = 35
        Dim rng As String
        rng = "C30:E34" ',E29:E34"
       sqlTransferFromExcel = "UPDATE TableA T1 " & _
                "INNER JOIN (SELECT F1, F3 FROM [{{connString}}].[{{sheetName}}${{rng}}]) T2 " & _
                "ON T2.F1 = T1.blkName_Override " & _
                "SET " & _
                "T1.fldVaue_Override = T2.F3 "
        
        sqlTransferFromExcel = Replace(sqlTransferFromExcel, "{{rng}}", rng)
        sqlTransferFromExcel = Replace(sqlTransferFromExcel, "{{connString}}", strCon)
        sqlTransferFromExcel = Replace(sqlTransferFromExcel, "{{sheetName}}", sht.Name)
        open_DB (True)

        db.Execute sqlTransferFromExcel

 

End Sub

Answer 1

等待大神答复