How do I keep track of which query is running?

[Seti]

I have a macro that runs a couple dozen queries. It takes about 30 minutes to run. I'd really like to have the status bar updated periodically showing me some info on where I am in the macro. If not the status bar, I'd settle for a dialog box. I don't need anything fancy, just some way to keep track of where I am.

I can live with code that I need to manually put a message or text into and that I would need to call between each query if need be. I am very flexible on this. Any and all suggestions would be greatly appreciated. Thanks so much.

Access 2000/Win 2K.

Seti

 

[Corticus]

Hi Seti,

That's a cool idea. Here's one idea. You could have a form pop up when the user initiates the macro. Put nothing but a listbox on the form and two buttons. Turn all the navigation, record selector, scroll bars etc. Then add something like this to the form load event:

private sub form_load()
Listbox.RowSourceType = "Value List"
end sub

Then make one button a start macro button and have it initialize your macro, but put in the code, after every SQL expression you run, something to the effect of

sub macro1
lbstrint = "Query Initialized, please wait..." & Now() & ";"
Listbox.rowsource = lbstring
SQL1
lbstring = lbstring & "Query 1 completed: " & now() & ";"
Listbox.rowsource = lbstring
SQL2
lbstring = lbstring & "Query 2 completed: " & now() & ";"
Listbox.rowsource = lbstring
SQL3
lbstring = lbstring & "Query 3 completed: " & now() & ";"
Listbox.rowsource = lbstring
end sub

and so on. You'll have to adjust the reference to your listbox to be absolute, since you will most likely need to initialize it from a module, unless you insert the macro into the form's code.

HTH,

Then in the macro who progress you want to moniter

 

[Seti]

Thanks for the quick reply. I am in a slightly different place than I may have implied. This macro is run from the "macro window". I am not using forms, just plain vanilla Access.

 

[Corticus]

I know nothing about Access's macro builder. Sorry

Though you could still use my method with the builder, I just don't know how. The idea is, after every query, you add one more item to the listbox rowsource with that item being a statement that says your done with Query X.

I understand you are not using forms. My suggestion was to use one.

-Corticus