sql - Access 中的 UPDATE 表从 Excel 范围获取数据

Question

我正在尝试更新 Access 中的表，从 Excel 中的范围（rng）中获取数据。我将 Brad Importing Excel 工作表范围中的宏调整为 Ms Access 表 ，但我收到运行时错误“3219”。如果我将 sqlTransferFromExcel = "UPDATE TableA T1" 替换为 "Select FROM TableA T1 "，它就可以工作。我想这个问题喜欢 UPDATE 语句。这里的代码：

    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

标签： sqlexcelvbams-access-2010