Status Bar to Show Query Progress?

[smedvid]

I have a user that has requested that I update the Status Bar with a Time (i.e. Now()) as specific increments to show that the query is still running. Can this be done; if so, how? via a Timer Event perhaps?

The SQL Insert I have take almost 1 hour to run due to volume of data. SQL has been optimized per Indexes, etc.

I presently use the following approach for Status Bar ...
lcSQL = "Delete From ..."
SysCmd acSysCmdSetStatus, "Task 1 of 6 - Delete Data - " & Now() '
ThisDB.Execute lcSQL, dbFailOnError

lcSQL = "INSERT INTO ..." 'Long Running Query!!!
SysCmd acSysCmdSetStatus, "Task 2 of 6 - Populating Data - " & Now()
ThisDB.Execute lcSQL, dbFailOnError

lcSQL = "DELETE FROM ...;"
SysCmd acSysCmdSetStatus, "Task 3 of 6 - Delete Data from table x - " & Now()
ThisDB.Execute lcSQL, dbFailOnError

SysCmd acSysCmdClearStatus


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[markgrizzle]

Access is single-threaded, which means that execute command has to complete before anything else happens.

I can't tell if you're using ADO or DAO, but ADO does have some asynchronous features which will let you do other things while the code's running (notify user, etc.).

The way I usually handle this is to perform the insert in a loop, and notify the user via the form's caption every 50 records or so, so they know something's happening.

do until rs.eof

if iCounter Mod 50 = 0 then
frm.Caption = "Loading " & rs.FieldName
DoEvents
end if

rs.MoveNext

loop

Though this slows down the operation, the user perceives it as faster since something's actually happening on screen.