JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 788
- Office Version
- 365
- Platform
- Windows
Hi,
i have the following which works fine:
but im wondering how i can modify this to UPDATE the existing record if the data2 value already exists in the table (Column2)? To prevent duplicate data.
Column1 in the table is Auto Incrementing primary key
Column2 - i want this to stay unique i.e if same value is attempted to be inserted - instead update previous
appreciate any help
i have the following which works fine:
but im wondering how i can modify this to UPDATE the existing record if the data2 value already exists in the table (Column2)? To prevent duplicate data.
Column1 in the table is Auto Incrementing primary key
Column2 - i want this to stay unique i.e if same value is attempted to be inserted - instead update previous
Code:
Sub insertSQL()
Application.ScreenUpdating = False
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306;"
Rw = 2
For i = 2 To Rw
data2 = Sheet3.Cells(i, 2).Value
data3 = Sheet3.Cells(i, 3).Value
data4 = Sheet3.Cells(i, 4).Value
data5 = Sheet3.Cells(i, 5).Value
data6 = Sheet3.Cells(i, 6).Value
data7 = Sheet3.Cells(i, 7).Value
MYSQL = "INSERT INTO TABLE1 (Column2, Column3, Column4, Column5, Column6, Column7) VALUES ('" & data2 & "','" & data3 & "','" & data4 & "','" & data5 & "','" & data6 & "','" & data7 & "');"
cnn.Execute MYSQL
Next i
cnn.Close
Set cnn = Nothing
Application.ScreenUpdating = True
End Sub
appreciate any help