VBA code - check if a record already exists in SQL table before inserting

ancabanca

New member
Joined
May 31, 2021
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,

I have an excel table with 5 columns and 3 rows with data. Every time the data changes it is uploaded to DB. Below is my code to send data from excel table to existing table in SQL. The challenge is, that the database excepts only unique ID values. I would be grateful for your help how to change the code that when ID in an excel table already exists in the DB, the code terminates with the msg "Duplicates are not allowed!".

Code:
Sub IMPORT()
 
   Dim con As ADODB.Connection
   Dim cmd As ADODB.Command
  
   Set con = New ADODB.Connection
   Set cmd = New ADODB.Command
  
   Dim r As Range
  
   con.ConnectionString = sqlconstr
   con.Open "Provider=sqloledb;Data Source=DS;Initial Catalog=AUTO;Integrated Security=SSPI"
  
   cmd.ActiveConnection = con
  
   For Each r In Range("a2", Range("a2").End(xlDown))
  
        If r.Offset(0, 4).Value <> "" Then
 
             cmd.CommandText = cmd.CommandText & _
               GetInsertText( _
                  r.Offset(0, 0).Value, _
                  r.Offset(0, 1).Value, _
                  r.Offset(0, 2).Value, _
                  r.Offset(0, 3).Value, _
                  r.Offset(0, 4).Value)
                  
         End If
       
    Next r
 
   Debug.Print cmd.CommandText
  
   cmd.Execute
  
   con.Close
   Set con = Nothing
           
   msgbox "Import successful"
       
End Sub
 
 
 
Function GetInsertText(ID As String, Date1 As Date, Date2 As Date, Reference As String, Price As Double)
 
 
   sql = _
       "insert into dbo.TP (" & _
       "ID, Date1, Date2, Reference, Price)" & _
       "values (" & _
       "'" & ID & "'," & _
       "'" & Format(Date1, "yyyy-mm-dd") & "'," & _
       "'" & Format(Date2, "yyyy-mm-dd") & "'," & _
       "'" & Reference & "'," & _
       Replace(Format(Price, "#0.00"), ",", ".") & ");"      
             
       GetInsertText = sql
 
End Function
 
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Write a function to read that id, if it returns a value then stop at that point.
 
Top