Re: Jet and Batch Updates

From: Jared (Jared_at_discussions.microsoft.com)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 06:17:10 -0800

You were right!

"Christoph Basedau" wrote:

> 21.01.2005 18:05, Jared schrieb:
> > I'm trying to use batch updating on a Jet database.
> > I'm able to open a recordset with locktype=adlockbatchoptimistic.
> > However, when I get to my second "AddNew" statement, I get an error message
> > stating that the "Number of rows pending changes exceeds the limit".
>
> This error occurs when the recordsets CurosrLocation is adUseServer,
> which is default. If you change it to adUseClient, Jet supports
> BatchUpdates.
>
> > Does Jet support batch updating?
>
> Works for me, See Testcode below
>
>
> Private Sub Form_Load()
>
> Dim con As ADODB.Connection
> Dim rs As ADODB.Recordset
>
> Set con = New Connection
> With con
> .CursorLocation = adUseClient
> .Provider = "Microsoft.Jet.OLEDB.4.0;"
> .Properties("Data Source") = "C:\Data\db4.mdb"
> .Open
> End With
> Set rs = New ADODB.Recordset
> With rs
> .CursorLocation = adUseClient '<- necessary for BatchUpdate+Jet
> .CursorType = adOpenStatic '<-only available cursor that supports updates
> ' if Location is clientside
> .Source = "tb1"
> .LockType = adLockBatchOptimistic
> Set .ActiveConnection = con
> .Open , , , , adCmdTable
> End With
> '# tb1 has 2 columns: PName(text) and PZip(long) and an autoID
> rs.AddNew Array("PName", "PZip"), Array("Smith", 69115)
> rs.AddNew Array("PName", "PZip"), Array("Miller", 10585)
> rs.UpdateBatch ' <- if you comment out this line, new data will be
> ' available in the rcordset only, but not in the database
> rs.Requery 'Requery data from source
>
> AutoRedraw = True
> Print rs.GetString
>
> End Sub
>
>
>
>
> --
> Gruesse, Christoph
>
> Rio Riay Riayo - Gordon Sumner, 1979
>

Relevant Pages

  • Re: How to make updatable subform
    ... your example is with JET and not with SQL-Server - the subject ... nothing and is outside of the topic of this newsgroup is a better example. ... Dim con As ADODB.Connection ... If .RecordCount = 1 Then ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Clustered index=PK not returned by OpenSchema
    ... Doug Steele, Microsoft Access MVP ... > Dim Con As ADODB.Connection ... >> I'm using ADO's OpenSchema method with adSchemaIndexes on a Jet 4.0 ...
    (microsoft.public.access.gettingstarted)
  • Re: Clustered index=PK not returned by OpenSchema
    ... > In Jet the PK is always the clustered index. ... >> Dim Con As ADODB.Connection ... Am I correct in assuming the primary key will be the clustered ...
    (microsoft.public.access.gettingstarted)
  • 17 bandsaw
    ... Delta, Grizzly, Shop Fox and Jet. ... Anyone have comments pro or con? ...
    (rec.crafts.woodturning)