Progress bar in MS Access

Question

I have a query running in Microsoft Access 2010 and it takes over 30 minutes to run normally. I would like to present the end user with some status of the query. A progress bar would be nice but not required. Access seems to be poorly threaded and locks up tight during the execution of the query, negating any updates I try. While I'd rather whip out VS and write my own app to do this, I'm forced to use Access.

I used to run this from a batch script which populated the database but I'd like to have it all self-contained in Access. To be specific, the "query" is really a VBA script that pings a series of hosts. So I'm not too concerned about optimizing the time per se but simply about letting the end user know it hasn't locked up.

Answer 1

I often do something like this

Dim n As Long, db As DAO.Database, rs As DAO.Recordset  'Show the hour glass DoCmd.Hourglass True  Set db = CurrentDb Set rs = db.OpenRecordset("SELECT ...")  rs.MoveLast 'Needed to get the accurate number of records  'Show the progress bar SysCmd acSysCmdInitMeter, "working...", rs.RecordCount  rs.MoveFirst Do Until rs.EOF     'Do the work here ...      'Update the progress bar     n = n + 1     SysCmd acSysCmdUpdateMeter, n      'Keep the application responding (optional)     DoEvents      rs.MoveNext Loop rs.Close: Set rs = Nothing db.Close: Set db = Nothing  'Remove the progress bar SysCmd acSysCmdRemoveMeter  'Show the normal cursor again DoCmd.Hourglass False 

Note: Of course you must do the work programmatically for this to work. You cannot watch a runnging query in code or the like in Access. Possibly you could split the work of your slow query into smaller pieces, in order to get the chance of updating a progress bar. But you can always show the hour glass; this tells the user that something is happening.