ADO does not complete updating table

[Guest]

I have an interesting problem that I have never come across before. I am
using ADO to update a table with about 90,000 records. Basically, for each
record I use a Do Until rst.eof to
-take a field in the table
-combine it with input from the user
-do a detailed calculation involving a dynamic array (redimed each time)
-output the result back into another field in the table

The problem is that Access2003 gets through about 10,000 records before it
mysteriously completes processing.

Could this be a time-out issue or a memory problem? I get no error messages
and the exact record that the program completes processing on sometimes
varies.

Any thoughts as to what could be causing this?

Thanks in Advance.

 

[John Nurick]

When you say "mysteriously completes processing" do you mean that the
loop runs 90,000 times but does not process all the records, or that the
loop only runs about 10,000 times?

Have you tried ordinary debugging techniques, e.g,

-Enable "Break on All Errors" in Tools|Options|General
-Disable error handling in your code
-Print status messages to the immediate window or a log file

 

[Guest]

Thanks John. I forgot to "break on all errors".

I am getting the error message "File sharing lock count exceeded. Increase
MaxLocksPerFile". Is there any way to temporarily increase the
maxlocksperfile beyond 9,500 using ADO code?

Thanks in Advance
Miles.

 

[RD]

I ran into that issue back in April of last year. John Epsom suggested:
Application.dbengine.idle 1

I found that it worked better than messing with the MaxLocksPerFile thingy. I
put this in my loop just before the "Next i" statement, ran my function and it
worked perfectly.

HTH,
RD

 

[Guest]

Thanks RD. I see this command requires DAO.

I tried it and got as far as about 15,000 records before it failed on me. I
did a bit of research and came across Microsoft Knowledge base article
815281. It describes how to use DAO to temporarily change the registry entry
(in my case for 80,000 records)

application,dbengine.setoption dbmaxlocksperfile, 80000

I put this in the form load event for the form that takes the user input and
it works.

Thanks again.
Miles.

 

[RD]

That's odd. I was updating over 800,000 rows in one table and 1.2 million in
another.

Well, as long as the cat gets skinned, huh?

Glad you worked it out.

RD