ADODB Record Upate

Hello,

I am using ADODB to create new record in an access db. Now, that I'm past that hurdle, how do I check if the unique key already exists and then update that record? Any help would be greatly appreciated.

Private Sub CommandButton1_Click()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
'Dim db As ADODB.Database, rs As DAO.Recordset, r As Long
Dim r As Long
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

Set db = New ADODB.Connection
db.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" & _
"C:\InspectionDB.accdb;"


    db.Open
    Set rs = New ADODB.Recordset
    
    rs.Open "FinalInspection", db, adOpenDynamic, adLockOptimistic
    r = 2 ' the start row in the worksheet
  
            rs.AddNew ' create a new record
            ' add values to each field in the record
            rs.Fields("Job Number") = Range("A" & r).Value  ' Unique Key
            rs.Fields("Inspection Date") = Range("B" & r).Value
            rs.Fields("SHIP DATE") = Range("C" & r).Value
            
    rs.Close
    Set rs = Nothing
    db.Close

End Sub

Mike,

Thank you. Exactly what I was trying to do.

Mike,

I finally have played with the code a little. I get a Run-time error 3219. If I add .Update before the 'end if' it throws a Run-time error -214217887 (80040e21) - "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again." Where am I going wrong? Here is my code below:

   db.Open
    Set rs = New ADODB.Recordset

    rs.Open "Select [Job Number], [Inspection Date], [SHIP DATE] " & _
         "From FinalInspection " & _
         "Where((([Job Number])= '1'));", db, adOpenDynamic, adLockOptimistic   '< '1' for text field in where clause
                                                                             '<  1  for numeric field in where clause
    
    'rs.Open "FinalInspection", db, adOpenDynamic, adLockOptimistic
    r = 2 ' the start row in the worksheet
  
    With rs
          If Not .EOF Then '<== ' Unique Id exists
             
            '.Fields("Job Number") = Range("A" & r).Value 'Unique key
            .Fields("Inspection Date") = Range("B" & r).Value
            .Fields("SHIP DATE") = Range("C" & r).Value
           
            .Update ' stores the new record 
            
          Else '<== Unique id does not exist
            
            .AddNew ' add values to each field in the record
            .Fields("Job Number") = Range("A" & r).Value 'Unique key
            .Fields("Inspection Date") = Range("B" & r).Value
            .Fields("SHIP DATE") = Range("C" & r).Value

          End If
     .Close
    End With
    Set rs = Nothing
    db.Close
End Sub

Mike,

I made a mistake. I've tried your method in your last reply. If I add .Update before the 'end if' it throws a Run-time error -214217887 (80040e21) - "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again."

Mike,

I've had some time to play with the code. For some reason the EOF if statement is skipped even if the record exists. Any ideas?