batchupdate 
Author Message
 batchupdate

I'm converting from DAO to ADO and generally things are going well. However,
I was recently stuck on using '.Addnew' and '.Update'.  I did not get any
errors, but the new records did not seem to make it back to the table. After
a couple of test runs through I got an error, something like: "The number of
rows with pending changes has exceeded the set limit". I changed '.Update'
to '.UpdateBatch' and things worked. What was going on, and is .UpdateBatch
the way to go?

Thanks

Some sample code is:

Global Const cTime As Date = "1/1/2000 2:20:00 AM"

Sub add_Records()
    'Dim rstTrack As New ADODB.Recordset
    Dim rstTrack As ADODB.Recordset
    Dim strConn As ADODB.Connection
    Dim cmdCommand As ADODB.Command
    Dim dteDate As Date
    Dim sngLat As Single, sngLon As Single
    Dim strID As String

    Set rstTrack = CreateObject("ADODB.Recordset")

    dteDate = cTime + "0:01:00"
    With rstTrack
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenDynamic
        .LockType = adLockBatchOptimistic
        .Open "dbo_MasterTrack"
    End With

    sngLat = 43.85616
    sngLon = -70.10504

    For i = 146 To 500
        With rstTrack
            .AddNew
            .Fields("ID").Value = i
            .Fields("Time").Value = dteDate
            .Fields("lat").Value = sngLat
            .Fields("lon").Value = sngLon
            .Fields("vehicle_ID").Value = 1
            .Fields("vehicle_status").Value = 3
            .Fields("vehicle_speed").Value = 45
            .Fields("vehicle_heading").Value = 0
            .Fields("FeatureID").Value = 2
            .UpdateBatch
            '.Update

' Here I tested to see if things were working:
' Show the newly added data.
'      MsgBox "New record: " & rstTrack!id & " " & _
'         rstTrack!lat & " " & rstTrack!lon

        End With
        dteDate = dteDate + "0:01:00"
        sngLat = sngLat + 0.0005
        sngLon = sngLon - 0.0005

    Next

    rstTrack.Close
End Sub



Sat, 12 Jul 2003 23:38:39 GMT  
 batchupdate
It might have  to do with using the
 .LockType = adLockBatchOptimistic

The docs imply that will defers updates until a "batch" Update.

However the docs also say that UpdateBatch should only be
used with Static or Keyset cursors, not Dynamic.

You would not normally used UpdateBatch the way you are doing it.

It makes sense to use UpdateBatch when you modify some NUMBER of
records (rather than just one), then UpdateBatch will transmit the
changes to the database for all of the modified records.

Quote:

> I'm converting from DAO to ADO and generally things are going well. However,
> I was recently stuck on using '.Addnew' and '.Update'.  I did not get any
> errors, but the new records did not seem to make it back to the table. After
> a couple of test runs through I got an error, something like: "The number of
> rows with pending changes has exceeded the set limit". I changed '.Update'
> to '.UpdateBatch' and things worked. What was going on, and is .UpdateBatch
> the way to go?

> Thanks

> Some sample code is:

> Global Const cTime As Date = "1/1/2000 2:20:00 AM"

> Sub add_Records()
>     'Dim rstTrack As New ADODB.Recordset
>     Dim rstTrack As ADODB.Recordset
>     Dim strConn As ADODB.Connection
>     Dim cmdCommand As ADODB.Command
>     Dim dteDate As Date
>     Dim sngLat As Single, sngLon As Single
>     Dim strID As String

>     Set rstTrack = CreateObject("ADODB.Recordset")

>     dteDate = cTime + "0:01:00"
>     With rstTrack
>         .ActiveConnection = CurrentProject.Connection
>         .CursorType = adOpenDynamic
>         .LockType = adLockBatchOptimistic
>         .Open "dbo_MasterTrack"
>     End With

>     sngLat = 43.85616
>     sngLon = -70.10504

>     For i = 146 To 500
>         With rstTrack
>             .AddNew
>             .Fields("ID").Value = i
>             .Fields("Time").Value = dteDate
>             .Fields("lat").Value = sngLat
>             .Fields("lon").Value = sngLon
>             .Fields("vehicle_ID").Value = 1
>             .Fields("vehicle_status").Value = 3
>             .Fields("vehicle_speed").Value = 45
>             .Fields("vehicle_heading").Value = 0
>             .Fields("FeatureID").Value = 2
>             .UpdateBatch
>             '.Update

> ' Here I tested to see if things were working:
> ' Show the newly added data.
> '      MsgBox "New record: " & rstTrack!id & " " & _
> '         rstTrack!lat & " " & rstTrack!lon

>         End With
>         dteDate = dteDate + "0:01:00"
>         sngLat = sngLat + 0.0005
>         sngLon = sngLon - 0.0005

>     Next

>     rstTrack.Close
> End Sub



Mon, 14 Jul 2003 03:56:36 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. BatchUpdate Timeout problem

2. ADO question? BatchUpdate

3. ADO BatchUpdate problem

4. BatchUpdate(only one table)

5. Is It Possible to Batchupdate a Bound Grid?

6. RDO Batchupdate using Grid Bound Mode

7. Batchupdate and Rdo

8. HELP! Run-time error 40069 with BatchUpdate

9. Error with BatchUpdate

10. Recordset Batchupdate with Unique Constraint

11. Working with identity fields and BatchUpdated

12. E_FAIL when BATCHUPDATE..

 

 
Powered by phpBB® Forum Software