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!".
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: