更新excel vba数据库插入前后的数据库连接

Question

我目前有一个工作表，从表a中提取不存在于表B中的所有记录，然后我有一个宏将这些值插入到表B中。

但是我意识到，如果在插入宏之前不刷新查询，它将产生重复。

使用当前的宏，我如何在插入之前和之后包含刷新?

Sub Insert_New_Bills()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sBILL_NUM, sROCKTENN_DOC, sACTION, sNOTE1, sNOTE2 As String
    With Sheets("NEW BILLS")
        'Open a connection to SQL Server
        conn.Open "Driver={IBM DB2 ODBC DRIVER};Database=BROWN;Hostname=192.168.100.44;Port=50000;Protocol=TCPIP;Uid=" & "User" & ";Pwd=" & "Maddox2009" & ";CurrentSchema=LYNX;"
        'Skip the header row
        iRowNo = 2
        'Loop until empty cell in COLUMN1
        Do Until .Cells(iRowNo, 2) = ""
            sBILL_NUM = .Cells(iRowNo, 2)
            sROCKTENN_DOC = .Cells(iRowNo, 3)
            sACTION = .Cells(iRowNo, 4)
            sNOTE1 = .Cells(iRowNo, 5)
            sNOTE2 = .Cells(iRowNo, 6)
            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "INSERT INTO OH_CU_WR_TEMPLATE (BILL_NUMBER, ROCKTENN_DOC, ACTION, NOTE1, NOTE2) values ('" & sBILL_NUM & "','" & sROCKTENN_DOC & "', '" & sACTION & "', '" & sNOTE1 & "', '" & sNOTE2 & "')"

            iRowNo = iRowNo + 1
        Loop
        MsgBox "RECORD UPDATED"
        conn.Close
        Set conn = Nothing
        Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    End With
End Sub

Answer 1

无论前后刷新，SQL语句本身都不会避免重复。如果你有一个主键或复合索引，限制在插入的单元格值中使用的重复值，你可能会遇到一个错误，并破坏你的代码，因为当前没有设置错误/异常处理。

考虑填充一个经常被清理的临时表(最终表的精确结构副本)。然后，运行经典的LEFT JOIN ... NULL / NOT EXISTS / NOT IN追加查询来插入唯一的值。此外，由于我不鼓励，下面使用ADO参数化:

SQL (运行一次)

CREATE TABLE OH_CU_WR_TEMPLATE_TEMP AS (
    SELECT * FROM OH_CU_WR_TEMPLATE
) WITH NO DATA

VBA

    Dim cmd As ADODB.Command
    Dim strSQL As String
    ...
    ' CLEAN OUT TEMP TABLE
    conn.Execute "DELETE FROM OH_CU_WR_TEMPLATE_TEMP"
    ' PREPARE STATEMENT (INSERTS INTO TEMP TABLE)
    strSQL = "INSERT INTO OH_CU_WR_TEMPLATE_TEMP (BILL_NUMBER, ROCKTENN_DOC, " _
                      & " ACTION, NOTE1, NOTE2)" _
                      & " VALUES (?, ?, ?, ?, ?)"
    ' ITERATE ROWS
    Do Until .Cells(iRowNo, 2) = ""
        ' ASSIGN VALUES
        sBILL_NUM = .Cells(iRowNo, 2)
        sROCKTENN_DOC = .Cells(iRowNo, 3)
        sACTION = .Cells(iRowNo, 4)
        sNOTE1 = .Cells(iRowNo, 5)
        sNOTE2 = .Cells(iRowNo, 6)
        ' UPDATE CMD OBJECT 
        Set cmd = New ADODB.Command
        With cmd
            .ActiveConnection = conn
            .CommandText = strSQL
            .CommandType = adCmdText
            .CommandTimeout = 15
        End With
        ' BIND PARAMETERS
        cmd.Parameters.Append cmd.CreateParameter("param1", adVarChar, adParamInput, 255, sBILL_NUM)
        cmd.Parameters.Append cmd.CreateParameter("param2", adVarChar, adParamInput, 255, sROCKTENN_DOC)
        cmd.Parameters.Append cmd.CreateParameter("param3", adVarChar, adParamInput, 255, sAction)
        cmd.Parameters.Append cmd.CreateParameter("param4", adVarChar, adParamInput, 255, sNOTE1)
        cmd.Parameters.Append cmd.CreateParameter("param5", adVarChar, adParamInput, 255, sNOTE2)
        ' EXECUTE SQL
        cmd.Execute
        iRowNo = iRowNo + 1
    Loop
    ' NON-DUPLICATE APPEND QUERY (USE APPROPRIATE JOIN IDs)
    conn.Execute "INSERT INTO OH_CU_WR_TEMPLATE" _
                  & " SELECT * FROM OH_CU_WR_TEMPLATE_TEMP temp" _
                  & " LEFT JOIN OH_CU_WR_TEMPLATE main" _
                  & " ON temp.ID = main.ID" _
                  & " WHERE main.ID IS NULL;"