Excel VBA 向Access更新数据库

allanneo

如题，由于需要将每天新的报表数据更新到access数据库中tblOrder表中，由于可能存在数据重复，所以我写了个SQL，但怎么也不成功
strSQL = "INSERT INTO tblOrder SELECT A.* FROM [Excel 12.0;Database=" & wb.FullName & ";].[Order$" & "A2:P" & lr _
            & "] A LEFT JOIN tblOrder B ON A.[SO]=B.[SO] WHERE B.[SO] IS NULL"

哪位大神能否帮忙看看
我把excel文件放入附件里，数据库比较大，我想办法找个网盘上传

w8899

Sub 将工作表数据自动导入ACCESS数据库()
'引用Microsoft ActiveX Data Objects 2.x Library
    Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset, SQL As String, strMsg As String
    '    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\数据库.accdb"
    Dim DatabaseFuPass As String: DatabaseFuPass = ThisWorkbook.Path & "\数据库.accdb"
    Const myPass As String = "1234"
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=" & myPass & ";Data Source=" & DatabaseFuPass

    SQL = "SELECT A.* FROM [Excel 12.0;Database=" & ThisWorkbook.FullName & ";].[数据库$A1:X" & Sheets("数据库").Range("b" & Sheets("数据库").Rows.Count).End(xlUp).Row _
          & "] A LEFT JOIN (Select * From 数据库) D ON A.单号=D.单号  WHERE D.单号 IS NULL"
    rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
        SQL = "INSERT INTO 数据库 " & SQL
        cnn.Execute SQL
        strMsg = rs.RecordCount & "条记录已添加到数据库！"
    Else
        strMsg = "没有发现可以插入的记录！"
    End If
    MsgBox strMsg, vbInformation, "提示"
    rs.Close: cnn.Close
    Set rs = Nothing: Set cnn = Nothing
End Sub

w8899

你不知道搞个副本，删除里面的数据再传吗？