You can't really get progress of individual SQL statements. The best
you can do is monitor progress on another database connection by reading
uncommitted data, which I don't recommend.
However, you can include RAISERROR ... NOWAIT statements provide
feedback in a multi-step proc. With ADO, this requires that you to
handle the InfoMessage event to get the progress messages. I don't know
much about Access so I can't provide a code example. Sample proc:
CREATE PROC usp_UpdateStuff
RAISERROR ('usp_UpdateStuff starting', 0, 1) WITH NOWAIT
RAISERROR ('Table1 update starting', 0, 1) WITH NOWAIT
UPDATE Table1 ...
RAISERROR ('Table1 update completed', 0, 1) WITH NOWAIT
RAISERROR ('Table2 update starting', 0, 1) WITH NOWAIT
UPDATE Table2 ...
RAISERROR ('Table2 update completed', 0, 1) WITH NOWAIT
RAISERROR ('usp_UpdateStuff completed', 0, 1) WITH NOWAIT
Hope this helps.
SQL Server MVP
SQL FAQ links (courtesy Neil Pike):
> I have an application that uses Access 2000 as a front-end
> and SQL 2000 as the server. I have a process that updates
> around 75,000 records. The client calls a stored proc.
> and all processing is done on the server. Is there any
> way to provide feedback such as estimated time remaining
> or display a progress bar for the user. Any help would be