Unanswered: Unable to do multiple updates on a recordset
Can anyone help me? I take a recordset from an Access Database Object and place it in an array. I then sort the array. When i try to overwrite the recordset with the now ordered array with the algorithm below i get the following error.
*** Key column information is insufficient or incorrect. Too many rows were affected by update. ***
I don't understand why this is happening..
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myPath
'*** Place data from the myRecordset into myArray and sort
index = 0
While Not myRecordset.EOF
myRecordset!field1 = myArray(index).field1
myRecordset!field2 = myArray(index).field2
myRecordset!field3 = myArray(index).field3
index = index + 1
I figured it out a little while after i posted the thread.. I wasn't using primary indexes in my access tables. So when i ordered the array and tried to copy the array back into the recordset one element at a time, I was creating duplicates when the element i was copying back existed in its pre-ordered position lower down the recordset.
I think this is similar to what you were pointing out in your solution, that i look for the 'key field'.
A lesson i have learnt is to always use a primary index!!!! Are there any cases it is not needed?