How to check for record by using ID, then if record exists update if not add new record

Question

I've create an excel userform to collect data. I have connected it to Access to dump the data. However I want to update Access every time a user presses the submit button.

Basically I need the Select statement to determine an id existence, then if it doesn't exists I need to use the INSERT to add the new row. I'm very new with any SQL so any help would be great.

Here is the code I have now, I need to adapt it to ADO.

Sub Update()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim StrSql As String

Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="Foam", ActiveConnection:=cnn, _
        CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
        Options:=adCmdTable





StrSql = "SELECT * FROM Foam WHERE FoamID = " & txtMyID
Set rst = CurrentDb.OpenRecordset(StrSql, dbOpenDynaset)

If (rst.RecordCount = 0) Then
     DoCmd.RunSQL "INSERT INTO Foam (ID, Part, Job, Emp, Weight, Oven) VALUES " & _
          "(" & txtID & ", '" & txtField1 & "', '" & txtField2 & "', '" & txtField3 & "', '" & txtField4 & "', '" & txtField5 & "' );"

End If

 ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing


End With

End Sub

Answer 1

I revised your code sample just enough to get it working on my system and tested this version in Excel 2007.

When I use a value for lngId which matches the id of an existing record, that record is opened in the recordset and I can update the values of its fields.

When lngId does not match the id of an existing record, the recordset opens empty [(.BOF And .EOF) = True]. In that situation, I add a new record and add the field values to it.

Sub Update()
    Const TARGET_DB As String = "database1.mdb"
    Dim cnn As ADODB.Connection
    Dim MyConn As String
    Dim rst As ADODB.Recordset
    Dim StrSql As String
    Dim lngId As Long

    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
    End With

    lngId = 4
    StrSql = "SELECT * FROM tblFoo WHERE id = " & lngId
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseServer
        .Open Source:=StrSql, ActiveConnection:=cnn, _
                CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
                Options:=adCmdText
        If (.BOF And .EOF) Then
            ' no match found; add new record
            .AddNew
            !ID = lngId
            !some_text = "Hello World"
        Else
            ' matching record found; update it
            !some_text = "Hello World"
        End If
        .Update
        .Close
    End With

    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub