VB Forum / Database Access / February 2008

You used the "wrong" Open-Parameters for this kind of task.

Look at this example:

'***Into a Form (using a copy of NWind, at: c:\NWind2.mdb)
Option Explicit

Private Cnn As Connection, Rs As Recordset

Private Sub Form_Load()
 Set Cnn = New Connection
 Cnn.CursorLocation = adUseClient
 Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\NWind2.mdb"

 Set Rs = New Recordset
 Rs.Open "Select * from Products",Cnn,adOpenStatic,adLockBatchOptimistic
 Debug.Print vbCrLf; "RecordCount after Select: "; Rs.RecordCount

 'here a simple Update of an existing Record (the first one)
 Rs!ProductName.Value = Rs!ProductName.Value & "X"

 'now we delete a record (the last one)
 Rs.MoveLast
 Rs.Delete

 'and finally an insert (Rs!ProductID is AutoInc, and left at Null here)
 Rs.AddNew
 Rs!ProductName.Value = "SomeNewRecord"

 Rs.Filter = adFilterPendingRecords
 Debug.Print vbCrLf; "Pending Records: "; Rs.RecordCount
 Do Until Rs.EOF
   Select Case Rs.Status
     Case adRecModified: Debug.Print "Update", Rs.Bookmark
     Case adRecNew: Debug.Print "Insert", Rs.Bookmark
     Case adRecDeleted: Debug.Print "Delete", Rs!ProductID.OriginalValue
   End Select
   Rs.MoveNext
 Loop

 On Error Resume Next
 Rs.UpdateBatch

 If Err Then 'Batch-update failed
   Rs.Filter = adFilterConflictingRecords
   'do your analysis here

 Else 'success
   Rs.Filter = adFilterAffectedRecords
   Debug.Print vbCrLf; "Affected Records: "; Rs.RecordCount

   'in case of success, the Deletes are included inside the filtered
   'RecordCount above, but inside the Loop you don't get them anymore
   Do Until Rs.EOF
     'here you can compare with the bookmarks above,
     'this way you should be able to verify, which records
     'were Inserts - and which Records were Updates (and as said,
     'Deletes are ot enumerated here anymore)
     Debug.Print Rs.Bookmark
     Rs.MoveNext
   Loop
 End If

 Rs.Filter = adFilterNone

 Rs.Close
 Cnn.Close
End Sub

Olaf