Updating Large Recordsets

[Guest]

I have an Access table with 2 million rows, and have created some code which
updates two fields -- using DAO, With Recordset and Update. After updating
about 500,000 records, I get an Error 3001. If I change the code to use an
SQL UPDATE statement, everything works fine. Is this error caused by some
underlying problem using the WITH statement with large recordsets? I update
records one at a time by looping through the data. Thanks.

 

[Guest]

hi,
serious problem. i don't know what the cause of it is but
i have run into it myself. i have several tables with 1
mill + records and we have a problem with access locking
up on us. at least you get an error. we have to call Dr.
Watson to end task.
the only solution i have found is to not try to do it all
at once. work in smaller records sets. yeah i know, real
hassel. i don't think it is access itself but maybe a
hardware problem....memory or something...since the
smaller recordsets do work and the big ones don't. at
least on my pc.
this don't answer your question but maybe give you a work
around till you do get it answered. yeah, i know...piss
poor work around.
good luck.

 

[Guest]

Hi Bob,

I'm not sure what causes this, but I suspect it has something to do with
Access caching or record lock limits or something like that. I encountered a
similar problem in the past, and was able to get around it by using
transactions.

In that case, I began the transaction before going into the loop, kept a
counter of the number of updtates, and committed the transaction and reset
the counter every few hundred thousand records or so and it worked fine. So,
it seemed to be related to some type of limit in access that was being
exceeded (and in my case I was not able to get the sql update to work
either). It was almost like Access was keeping a rollback log as it would
with a normal update query, even though there was a request to update the
recordset for every record.

I'm not an expert on the inner workings of Access with regards to memory
allocation or caching, and I couldn't find any info on searches through
Google, so I never did find the cause (although I didn't really look into it
any further either). I hope that if any MVP's or other knowledgeable users
see this post they will add any info they may have. I would be very
interested myself.

-Ted Allen

 

[Guest]

Ted,

I did try increasing the file locks setting as I thought that might be a
problem, but it didn't help. I'll try the transaction idea. Thanks for the
suggestion.

Bob