ADODB Insert Query - Modify to Update instead ?

[JumboCactuar]

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

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

 

[Legacy 98055]

Does this table contain many records? If not, you could create an ADODB.Recordset, loop through, checking for a match of data2 in any record. If there is a match, update the record. If there is not a match on any data2 field, you loop to EOF, AddNew. Make sense?

Else, you could query prior to your existing insert, checking for the existence of a record that matches data2. If there's a match, update the record, else INSERT.

 

[Worf]

• For starters we need to determine if the record exists; SQL has an EXISTS statement but it is not supported on all situations. See example below for an alternative method.
• Next step will be the updating, logical candidate is the UPDATE clause.

Sub main()
MsgBox Jumbo("relation", "Sales", "30009")
End Sub


Public Function Jumbo$(sn$, fld$, t$)
Dim Conn As New ADODB.Connection, mrs As New ADODB.Recordset
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
mrs.Open "SELECT * From [" & sn & "$] WHERE " & fld & " =" & t, Conn
If mrs.EOF Then
    Jumbo = "not found"
Else
    Jumbo = "found"
End If
mrs.Close
Conn.Close
End Function

 

[JumboCactuar]

@Tom Schreiner
@Worf

Thankyou
Table only contains around 1000 records currently but could soon be 20000+

Is there any advantage to using ADODB.recordset instead of direct query like I've done in my post?
It's something I've never used

 

[Worf]

From the Internet:

The Connection object is the top-level object in the ADO hierarchy. It represents the connection made to the data source through the OLE DB data provider, and handles all communication between your solution and a data source. Unlike the Connection object in DAO's ODBCDirect or in Remote Data Objects (RDO), you do not have to instantiate an ADO Connection object before you create other objects such as the Command or Recordset object. This lets you create temporary objects and associate them with a Connection object as needed.

The ADO Connection Object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.
If you want to access a database multiple times, you should establish a connection using the Connection object. You can also make a connection to a database by passing a connection string via a Command or Recordset object. However, this type of connection is only good for one specific, single query.

You open and close a Connection object by using the Open and Close methods. Additionally, Connection objects provide transaction support by using the BeginTrans, CommitTrans, and RollbackTrans methods. Use transactions when you want to save or cancel a series of changes made to the source data as a single unit. For example, if you are transferring money between two accounts, you want to make sure that both debiting and crediting operations occur. By making these changes within a transaction, you ensure that either all or none of the changes goes through.

The Recordset object represents a set of records returned from a query, and a cursor into those records.
You can open a Recordset object without explicitly opening a Connection object by passing a connection string to the Recordset object's Open method. However, if you create and open a Connection object, you can open multiple Recordset objects on the same connection. The Recordset object contains a Fields collection that contains Field objects, each of which represents a single column of data within a Recordset object.