Re: Jet and Batch Updates

From: Christoph Basedau (e_tonne_at_hotmail.com)
Date: 01/23/05 

Date: Sun, 23 Jan 2005 14:29:05 +0100

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: filter many fields with checkbox
    ... Private Sub Form_Load ... Set rssppd = New Recordset ... rssppd.ActiveConnection = con ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL Update Issue
    ... If you want to drop the SQL, here is the sample using recordset method. ... Dim con As New ADODB.Connection ...
    (microsoft.public.access.formscoding)
  • Re: Need help with a DAO to ADO conversion
    ... and loop through the Fields collection of the ... resultant recordset, looking at the names of each field. ... Doug Steele, Microsoft Access MVP ... If the BE's a Jet database, then it's not ODBC: ...
    (microsoft.public.access.formscoding)
  • Re: Row cannot be located for updating
    ... > result a recordset containing one row, ... > Strangly enough, the value that I changed, is changed in the database ... > Dim Con As New ADODB.Connection ... > 'Execute the query and remove the active connection ...
    (microsoft.public.data.ado)
  • Row cannot be located for updating
    ... result a recordset containing one row, ... Dim Con As New ADODB.Connection ... Con.Open ConnectionString ... Set Con = Nothing ...
    (microsoft.public.data.ado)