Disconnected Recordsets Grrrrrr

Hello,

I’m sure I’m missing the obvious here but for some reason after I open a disconnected recordset from the database and then edit one of the records, when I go to update the database with out changing the Primary Key data I get the following error
“Violation of PRIMARY KEY constraint 'PK_Test'. Cannot insert duplicate key in object 'Test'. “Err.Number –2147217900”

Code:

---

Public Sub OpenDb()

  With rs
    .ActiveConnection = "Provider=SQLOLEDB;" & _
                        "Data Source=SQL_001;" & _
                        "Database=Test;" & _
                        "UID=sa;" & _
                        "PWD=;"
    .CacheSize = 1
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Source = "Manufacture"
    .Open
  End With
 
  rs.ActiveConnection = Nothing

End Sub

Public Sub UpDateDb()

  If bIsDirty = True Then
    rs.ActiveConnection = "Provider=SQLOLEDB;" & _
                          "Data Source= SQL_001;" & _
                          "Database=Test;" & _
                          "UID=sa;" & _
                          "PWD=;"
    rs.UpdateBatch
    rs.ActiveConnection = Nothing
  End If

End Sub

---

Any help would be greatly appreciated

correct code is
With rsSave
.ActiveConnection = cn

Debug.Print .RecordCount
.Filter = adFilterPendingRecords
Debug.Print .RecordCount ' Filter auf geänderte Datensätze setzen
.UpdateBatch (adAffectGroup) ' nur geänderte Datensätze werden geschrieben

.Filter = adFilterConflictingRecords
Debug.Print .RecordCount ' Filter auf Datensätze mit Konflikt setzen
If .RecordCount > 0 Then ' Wenn Datensätze mit Konflikt vorhanden
With cn
'.BeginTrans
'If SolveConflicts(rsSave) Then ' Konfliktlösungsroutine aufrufen
'.CommitTrans
'Else
'.RollbackTrans
'End If
End With
End If
.Requery ' erneut abfragen (sonst werden Auto Values von Access im UI nicht aktualisiert)
.Filter = adFilterNone ' Filter deaktivieren

.ActiveConnection = Nothing
End With

Hello Maexchen,

Well I gave it a shot and I'm still getting the same error. I don't understand why it won't let me update the database.

Hello,

I found the source of my problem. I was updating the Primary Key value and SQL7 was trying to update that field. Well it couldn't do that so guess it tried to INSERT it as a new record, and of course it couldn't do that either so I got the error. Once I stopped playing around with the Pimary Key value it worked just fine.

Thanks,

Hello Maexchen,

I was looking at your code, and I was wondering how your SolveConflicts function worked. That is my next step and I was just curious how you implemented this. If you could be so kind as to give a general idea I would be very gatefull.

Best