Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Angry Unanswered: Unable to do multiple updates on a recordset

    Hi,
    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..

    With myConnection
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & myPath
    .Open
    End With

    myRecordset.CursorLocation = adUseClient
    strQuery = "SELECT * FROM Plans"
    myRecordset.Open strQuery, myConnection, adOpenStatic, &_ adLockOptimistic, adCmdText

    '*** 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
    myRecordset.Update
    myRecordset.MoveNext
    index = index + 1
    Wend

    '*** Error occurs within this while loop

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Verify you haven't added or changed any key field, then try inserting the following:

    myRecordset.FindFirst "<KeyField> = '" & myArray(index).<fieldN> & "'"
    myRecordset.Edit

    After the "While..." statement.

    <KeyField> is the column name of the key.

    <fieldN> is the array variable where you stored the key.

    If <fieldN> is numeric...you don't need the single quotes before and after.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Dec 2002
    Posts
    2

    Talking

    Thanks for the help Bruce..

    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?

  4. #4
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    There are many instances where you don't need/use the primary indexes.

    "SELECT * FROM CUSTOMERS WHERE SALES > 50000.00"

    Ad Hoc requests are usually generated by the user community.

    Indexed are usually critical to the IT community for maintenance.

    Good Luck,
    Bruce Baasch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •