SQL Insert or Update depending on if record is found ?

[JumboCactuar]

Hi,

I have the following code which inserts data into a table with ADODB connection or updates table depending on the if statement.

Sub Button1Click()

If Sheet3.Range("N3") <> True Then
    insertDATA
Else
    updateDATA
End If

End Sub

Sub insertDATA()

   Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306;"

    i = 2
    
    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
    
    SQLSTRING = "INSERT INTO TABLE1 (RECID,RECDATE,RECTIME,RECLOC,RECDATA,RECMEMO) VALUES ('" & Data2 & "','" & Data3 & "','" & Data4 & "','" & Data5 & "','" & Data6 & "','" & Data7 & "');"
    cnn.Execute SQLSTRING
    Next i
    
    cnn.Close
    Set cnn = Nothing
    
Sheet3.Range("N3") = True

End Sub

Sub updateDATA()

   Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306;"

    i = 2
    
    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
       
    SQLSTRING = "UPDATE TABLE1 " & _
    "SET RECID = '" & Data2 & "' " & _
    ",RECDATE = '" & Data3 & "' " & _
    ",RECTIME = '" & Data4 & "' " & _
    ",RECLOC = '" & Data5 & "' " & _
    ",RECDATA = '" & Data6 & "' " & _
    ",RECMEMO = '" & Data7 & "' " & _
    "WHERE RECID='" & Data2 & "';"
            
    cnn.Execute SQLSTRING
    
    Next i
    
    cnn.Close
    Set cnn = Nothing

End Sub

I think i can somehow do this all in one module using recordset somehow?

IF RECID is not found in table then INSERT else UPDATE

Does anyone know the best way to do this? Without locking the database longer than needed

any help appreciated

 

[JumboCactuar]

edit:
something like this but unsure how i can use with the above

If rs.EOF Then
    'Record not found so insert
     SQLSTRING = "INSERT INTO...."
Else
     'Record is found so update
     SQLSTRING = "UPDATE TABLE1...."
End If