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”


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