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

excel, ms-access, vba

Solution

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

Problem

Original source