How to find duplicate entries in table (SQL Server) in VBA

Question

I am inserting records into a SQL Server table from an Excel sheet through VBA code with the same column headers as in the SQL Server table.

There are 4 columns in the SQL Server table which should not have values to be inserted that already exist. Those columns on the table are SF Payroll ID, Unity Payroll Element Name, ICP/LMC Element Code & ICP/LMC Element Name.

Every time each row is being inserted from the Excel file the code needs to check, that inserting values for columns SF Payroll ID + Unity Payroll Element Name + ICP/LMC Element Code + ICP/LMC Element Name does not already exist.

If the row contains the same value that already exists on the column then that row should be rejected from being uploaded to the SQL Server table and the rest of the rows should be uploaded.

Below is my code which uploads the records into the SQL Server table. Kindly suggest how to code in VBA to validate this.

Private Sub PushToDB_Click()

Dim conn As ADODB.connection
Dim connString As String

connString = "Provider=SQLOLEDB;Server=DEEPAKSQL;Database=Workload;User Id=DHARMA;Password= ********"
Set conn = New ADODB.connection
conn.Open connString


Dim rowCountsheet As Integer
rowCountsheet = ActiveSheet.UsedRange.Rows.Count
Dim tempisheet As Integer
tempisheet = 2
Dim shsheet As Worksheet
Set shsheet = ThisWorkbook.Worksheets("Data copied")



On Error GoTo CleanFail
conn.BeginTrans

Dim rowCount As Integer
rowCount = ActiveSheet.UsedRange.Rows.Count
Dim tempi As Integer
tempi = 2

Dim sql As String
sql = " INSERT INTO [dbo].[ELEMENT_INFO] ([DB_Upload_Action_Key],[Account_Client_Customer_Name],[SF_Account_ID],[Payroll],[SF_Payroll_ID],[Record_Creation_Date],[Record_Creation_Time_At],[Record_Creation_Guardian_Name],[Record_Last_Modified_Date],[Record_Last_Modified_Time_At],[Record_Last_Modified_Guardian_Name]," _
     & "[Unity_Payroll_Element_Name],[Element_Description],[Element_Status],[Element_Type]," _
     & "[Element_Input_Classification],[Element_Input_Type],[Element_Frequency]," _
     & "[ICP_Or_LMC_Element_Code],[ICP_Or_LMC_Element_Name],[Source_Of_Data_Input_HCM_Integration_EUT_T_A_Other],[GL_Code_Debit],[GL_Code_Credit],[GL_Account_Name],[Comments])" & _
       "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
       
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Data copied")
Dim pctDone As Single
Dim iLabelWidth As Integer
iLabelWidth = 240

Do Until tempi = rowCount + 1
mapping_upload.Hide
frmProgressForm.Show
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = sql


cmd.Parameters.Append cmd.CreateParameter("DB_Upload_Action_Key", adVarChar, adParamInput, 100, sh.Cells(tempi, 1).Value)
cmd.Parameters.Append cmd.CreateParameter("Account_Client_Customer_Name", adVarChar, adParamInput, 250, sh.Cells(tempi, 2).Value)
cmd.Parameters.Append cmd.CreateParameter("SF_Account_ID", adVarChar, adParamInput, 250, sh.Cells(tempi, 3).Value)
cmd.Parameters.Append cmd.CreateParameter("SF_Payroll_ID", adVarChar, adParamInput, 250, sh.Cells(tempi, 6).Value)
cmd.Parameters.Append cmd.CreateParameter("Record_Creation_Date", adDBDate, adParamInput, 100, dateLabel.Caption) 'sh.Cells(tempi, 10).Value)
cmd.Parameters.Append cmd.CreateParameter("Record_Creation_Time_At", adDBTime, adParamInput, 100, timeLabel.Caption) 'sh.Cells(tempi, 11).Value)
cmd.Parameters.Append cmd.CreateParameter("Record_Creation_Guardian_Name", adVarChar, adParamInput, 100, TextBox1.Text) 'sh.Cells(tempi, 12).Value)
cmd.Parameters.Append cmd.CreateParameter("Record_Last_Modified_Date", adDBDate, adParamInput, 100, dateLabel.Caption) 'sh.Cells(tempi, 13).Value)
cmd.Parameters.Append cmd.CreateParameter("Record_Last_Modified_Time_At", adDBTime, adParamInput, 100, timeLabel.Caption) 'sh.Cells(tempi, 14).Value)
cmd.Parameters.Append cmd.CreateParameter("Record_Last_Modified_Guardian_Name", adVarChar, adParamInput, 100, TextBox1.Text) 'sh.Cells(tempi, 15).Value)
cmd.Parameters.Append cmd.CreateParameter("Unity_Payroll_Element_Name", adVarChar, adParamInput, 3500, sh.Cells(tempi, 16).Value)
cmd.Parameters.Append cmd.CreateParameter("Element_Description", adVarChar, adParamInput, 5000, sh.Cells(tempi, 17).Value)
cmd.Parameters.Append cmd.CreateParameter("Element_Status", adVarChar, adParamInput, 500, sh.Cells(tempi, 18).Value)
cmd.Parameters.Append cmd.CreateParameter("Element_Type", adVarChar, adParamInput, 5000, sh.Cells(tempi, 19).Value)
cmd.Parameters.Append cmd.CreateParameter("Element_Input_Classification", adVarChar, adParamInput, 5000, sh.Cells(tempi, 32).Value)
cmd.Parameters.Append cmd.CreateParameter("Element_Input_Type", adVarChar, adParamInput, 5000, sh.Cells(tempi, 33).Value)
cmd.Parameters.Append cmd.CreateParameter("Element_Frequency", adVarChar, adParamInput, 5000, sh.Cells(tempi, 39).Value)
cmd.Parameters.Append cmd.CreateParameter("ICP_Or_LMC_Element_Code", adVarChar, adParamInput, 5000, sh.Cells(tempi, 40).Value)
cmd.Parameters.Append cmd.CreateParameter("ICP_Or_LMC_Element_Name", adVarChar, adParamInput, 5000, sh.Cells(tempi, 41).Value)
cmd.Parameters.Append cmd.CreateParameter("Source_Of_Data_Input_HCM_Integration_EUT_T_A_Other", adVarChar, adParamInput, 5000, sh.Cells(tempi, 42).Value)
cmd.Parameters.Append cmd.CreateParameter("GL_Code_Debit", adVarChar, adParamInput, 5000, sh.Cells(tempi, 43).Value)
cmd.Parameters.Append cmd.CreateParameter("GL_Code_Credit", adVarChar, adParamInput, 5000, sh.Cells(tempi, 44).Value)
cmd.Parameters.Append cmd.CreateParameter("GL_Account_Name", adVarChar, adParamInput, 5000, sh.Cells(tempi, 45).Value)
cmd.Parameters.Append cmd.CreateParameter("Comments", adVarChar, adParamInput, 8000, sh.Cells(tempi, 50).Value)

cmd.Execute

tempi = tempi + 1

pctDone = (tempi - 1) / rowCount
frmProgressForm.lblProgress.Width = iLabelWidth * pctDone
frmProgressForm.FrameProgress.Caption = Format(pctDone, "0%")

DoEvents

Loop
Unload frmProgressForm
MsgBox "Data Loaded Successfully to T-1 DataBase", vbInformation, "SDD04 - Commit Transaction, Okay!"
mapping_upload.Show

conn.CommitTrans

CleanExit:
    conn.Close
    Exit Sub
    
CleanFail:
     conn.RollbackTrans
     MsgBox "Input file error either value exceeds or having wrong type. Transaction was rolled back. " & Err.Description, vbCritical, "SDD04 - Input error"
     Unload frmProgressForm
     Debug.Print Err.Number, Err.Description
     Resume CleanExit

End Sub

Answer 1

You are using a loop to reach every row, and building the INSERT INTO statement. That's right, but I don't see any code trying to check what you need: If that row already exists before inserting it.

So, what you need is to check, for every row, if it exists in your SQL table before inserting it. As you have to do it for every row, think that the first thing you should do in the loop before build the INSERT statement, is to build the SELECT statement to do the check, and then, depending on the result, you will do the INSERT or not.

I suppose you can build a SELECT statement from VBA if you are able to build an INSERT statement. Otherwise you can ask for help about it.

ADDED: 2022-05-20

Here is a code snippet that may help you with the SELECT statement.

Private Sub Test()

    Dim cnn As New ADODB.Connection
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim ConnectionString As String
    Dim strSql As String

    ConnectionString = "Provider=SQLOLEDB;Server=DEEPAKSQL;Database=Workload;User Id=DHARMA;Password= ********"
    strSql = "SELECT * FROM [dbo].[ELEMENT_INFO] WHERE [SF Payroll ID]=" & Value1 & " AND [Unity Payroll Element Name]='" & Value2 & "' AND ..."
    rst.CursorLocation = adUseClient ' Client-side cursor
    rst.Open strSql, cnn

    If Not rst.RecordCount > 0 Then
        'Not Exists -> Do the Insert
    Else
        'Exists -> Don't do the Insert
    End If

    rst.Close
    Set rst = Nothing

End Sub