Applicatin hangs on working with loarge data 3

[jobyk]

Hi

I have to create an MIS module which has to process thousands of records and need to do some calculation. My problem is when I put the recordset with more than 20,000 records into a loop, the system hangs. Now what i have done is i put a message box in every 500 records. The user has to press a key on the message box popup frequently. Is there any way I can run a loop for more 20,000 times without any user intervetion.

Thanks in advance

 

[hammy]

This is possibly too simple, however have you tried adding a DoEvents at the end of each loop?

Hammy

 

[mikewoodhouse]

Is it really hanging or just taking a loooooong time? Could you display a progress bar (assuming you know how many rows you're going to process) that's updated every 100 rows or so (you'll probably need a DoEvents, Refresh or Repaint or some combination of those to get the display to update)?

 

[dilettante]

This sounds like the answer all right, hammy/mikewoodhouse.

The program isn't "hanging" at all, but is becoming unresponsive. I'll bet the lack of display updates is the only serious problem though.

A progress-bar is the way to go as a pacifier for the user. And as a side-effect, a DoEvents every so often will both permit the progress-bar updates to be visible and take care of the display updates. Umm, things like cancel buttons would be able to do some good too then.

Puting in a DoEvents every time through the loop will totally destroy performance though. I'd put the DoEvents into the loop, but only execute it every so often:

For I = start To theend
  [do your work here]
  If I Mod 100 = 0 Then
    [progress update here]
    DoEvents
  End If
Next I

Then do some experimentation to determine whether 100 is the right factor, or perhaps something larger or even smaller. It sort of depends on how intensive your "work" on each item is, and how responsive you need the application to appear.

 

[jobyk]

It has worked with Progress bar and do events.

Thanks to all

 

[mattKnight]

jobyk

Perhaps you should "give a star" to those who helped?

 

[jobyk]

Hi

We use application roles with Nt authentication to connect to the Database and access the tables in SQL. The connection string uses the Nt authentication to connect to the DB and runs the sp_setapprole procedure to set the rights thru applicatuon role. It works fine with connection and record set.

But it doesn't workmwith ADODC. I can give the connection connection string in the adodc properties. But how do I run the application role to set the rights. Pl. help me. Thanks in advance.

Joby