Results 1 to 2 of 2

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

  1. #1
    Neophyte ancabanca's Avatar
    Join Date
    May 2021
    Posts
    1
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Bob Phillips; 2021-06-07 at 11:47 AM.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,855
    Articles
    0
    Excel Version
    O365
    Write a function to read that id, if it returns a value then stop at that point.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •