Update Database connection before and after database insert on excel vba

Question

I currently have a worksheet that pulls all records from Table A that do not exists within Table B, I then have a Macro that inserts those values into Table B.

But I realized that it will produce duplicates if the query is not refreshed before the macro is inserted.

With the current Macro I have, how can I include a refresh before and after the insert?

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

I am not sure about the syntax with `db2`. But I'd rather use a solution similar to this one: http://stackoverflow.com/questions/9911659/can-i-use-if-not-exists-to-insert-a-row-if-the-row-is-not-already-present-in-s Especially if you allow for multiple instances of the same Excel file you cannot control anymore which records have been inserted into the DB already and which ones are still pending. Other than that I'd implement a worksheet event to track which ones have been uploaded and which ones haven't been uploaded yet. But this solution wouldn't be as neat as the first one I proposed. — Ralph, Nov 09 '16 at 22:21

score 0 · Answer 1 · answered Nov 09 '16 at 22:59

Regardless of a refresh before or after, the SQL statement does not itself avoid duplicates. If you have a primary key or composite index that restricts duplicate values used in one of the inserted cell values, you may hit an error and break your code as no error/exception handling is currently set up.

Consider populating a temp table (exact structural replica of final table) that is routinely cleaned out. Then, run the classic LEFT JOIN ... NULL / NOT EXISTS / NOT IN append query to insert unique values. Also, as I would be remiss in not encouraging, below uses ADO parameterization:

SQL (run one-time)

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;"

Update Database connection before and after database insert on excel vba

1 Answers1