Processing time clock.

[leonepaolo]

Hi!

I have a process that takes a fairly long time. Is there a way to show a clock, a timer clock I suppose, that counts down the time the process is taking.

When I press the command button to run my process the clock, either digital or dial (both could be fun) would start to show time, like a timer.

Any help and/or suggestion are greatly appreciated.

Thanks in advance,
Paolo.

 

[TheAceMan1]

How are ya leonepaolo . . .

Do you know how much time its takes or does it vary?

Is there a max count of something that can be used?

Be aware . . . you could also use a [blue]Progress Bar![/blue]

See Ya! . . . . . .

 

[leonepaolo]

Hi TheAceMan1,

I'm great! How are you?

The last time I ran it, it took 58 seconds, not to long bear, but I'll be incorporating the process into another, which takes a "few" seconds of its own. Once that's done, it's going into another process... I love automation!

I use a lot of sql, so, while there's some looping, some of the sql statements take quite a while.

My original approach was to show the current action in a label but I can't seem to always get it to refresh. It seems to work until I start doing sql then it stops.

I've tried:
lbl.Caption = "New string"
DoEvents
I get that to work in VB6 but not Access.
I've also tried:
Me.Refresh
DoEvents

As for the Progress Bar, I've never used one. I'm not quite sure how successful it would be though because most of the work (and time taken) comes from the sql statements. The looping through the recordsets is pretty quick after that.


Thanks for your help,
Paolo.

 

[TheAceMan1]

leonepaolo . . .

Just trying to get something a little finite here. Getting a handle on this with a timer is not a good Idea due to differences in processor speed. However it looks as if you may be able to come up with a recordcount.

[blue]I use a lot of sql, so, [purple]while there's some looping[/purple], some of the sql statements take quite a while.[/blue]

Is this looping of SQL the result of looping thru a recordset?

See Ya! . . . . . .

 

[leonepaolo]

Hi TheAceMan1,

Too bad about processor speed affecting a timer.

My heavy sql statements come before the loops. The heaviest process reverses a date sort on 1.5 million records, and 8,000 new records get added every day. I decided to split this table, I only really need the 11 most recent days, about 100k records. This drops the first process to 6 seconds from 58 seconds.

I think I see where we could go with this. I'll probably use about 4, maybe 5 progress bars.
FYI:
Phase 1 take 15 seconds
Phase 2 Part 1, stripping it down the way I have takes about 15 seconds, but I'll be adding three more lighter process within it, 10 seconds or so each.
There are at least 5 more Phase to incorporate.
I figure,in the end, I'll be looking at close to a 5 minute process.

I guess I should really look into progress. Is there a good FAQ out there that you know of and maybe a few words to the unwise that you may wish to share.

Thanks for your help (hope I didn't run on too much),
Paolo

p.s.
Also, I can't figure out how set the connection or open the access mdb that I'm doing all this in to a Microsoft.Jet.OLEDB.4.0

In VB6 I do:
Set db = CreateObject("ADODB.Connection")
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\File.mdb
When I set my recordsets (rs) to this it allows me to use rs.sort = ("Date DESC") which seems a lot faster than what I doing now.

 

[TheAceMan1]

Roger That leonepaolo . . .

Despite my prior statement on using timers, it looks like thats just whats needed to clock the SQL's. We just have to fight processor speed here (3Ghz is faster than 1Ghz). I'm currently testing large recordsets withe the Timer event (only way to interupt an SQL/Query in progress). Until I return here'a a bit of the skinny on [blue]Progress Bars[/blue]:

There are three main operational attributes for a Progress Bar:

[purple]Min[/purple], which defaults to zero and usually needs no setup.

[purple]Max[/purple], the full count of the bar, usually set by the programmer . . . AKA . . . You!

[purple]Value[/purple] (and the most important), the current value of the bar, >= min & <= max that is [purple]setup/enumerated by you![/purple]


To setup the Form & ProgressBar, in a new form in design view, on the menubar click Insert - ActiveX Control..., scroll to and select [blue]Microsoft ProgressBar Control, version 6[/blue]. Click OK.

Size the bar to your liking then, right-click the bar - [blue]ProgCtrl Object[/blue] - [blue]Properties[/blue]. At the bottom you'll see the combobox for the [blue]Scrolling[/blue] property. Select [purple]Smooth[/purple]. Click OK.

Now set the following properties for the form:

[blue]ScrollBars [purple]Neither[/purple]
Record Selectors [purple]No[/purple]
Navigation Buttons [purple]No[/purple]
Auto Resize [purple]Yes[/purple]
Auto Center [purple]Yes[/purple]
Border Style [purple]Dialog[/purple]
Min Max Buttons [purple]None[/purple]
Caption [purple]Empty/No Value[/purple]
PopUp [purple]Yes[/purple]
Modal [purple]Yes[/purple][/blue]

Save/close the form

Use the following to open the form:

[blue]   DoCmd.OpenForm "YourFormName"
   DoEvents [green]'Allows ActiveX Control to fully refresh.[/green][/blue]

The following is an example of opening, enumerating, and closing the form:

[blue]   Dim frm As Form, ctl As Control, n As Long
   
   DoCmd.OpenForm "[purple][b]ProgressBarFormName[/b][/purple]"
   DoEvents [green]'Allows ActiveX Control to fully refresh.[/green]
   
   Set frm = Forms![purple][b]ProgressBarFormName[/b][/purple]
   Set ctl = frm![purple][b]ProgressBarName[/b][/purple]
   
   frm.Caption = "Down Loading Data!"
   ctl.Max = 1000 'Set max count for the bar
   
   [green]'Sample enumeration. Your enumeration code would go here.[/green]
   For n = 1 To 1000
      ctl = n
   Next
   
   DoCmd.Close acForm, "[purple][b]ProgressBarFormName[/b][/purple]"
   
   Set ctl = Nothing
   Set frm = Nothing[/blue]

See Ya! . . . . . .

 

[leonepaolo]

Thanks TheAceMan1,

Looks great!

I can't wait to try it.

Thank you,
Paolo