[AccessD] Queries Progress Bar

William,

    My sample db named  Query_TopsPerSubGrpLargeData  displays a progress bar, using option #2 suggested by Drew. It is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

    Interestingly, in case of large data set, piecemeal processing in convenient packets, through saved queries is found to be faster as compared to a conventional query acting upon the whole data, specially when it involves subqueries. Comparative performance of various alternatives is also brought out in the above sample (Access 2000 file format).

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Drew Wutka 
  To: Access Developers discussion and problem solving 
  Sent: Sunday, April 05, 2009 14:43
  Subject: Re: [AccessD] Queries Progress Bar


  I'd say you have the following options:

  #1. Put in an animation (.avi file).  This would have no accuracy at
  all.

  Nothing in Access is going to work while a query is running, because VBA
  is single threaded, so when you run the query, no other code in your
  project will run.

  #2.  Break the query down into steps you run manually in code, and thus
  can provide status points to display a progress.

  #3.  Run the query outside of Jet, I believe ADO (or maybe it's
  something else) will provide a progress.

  Both 2 and 3 are going to slow things down, because Jet is going to run
  optimized on it's own, these options are going to take longer.

  #4.  Jet is reporting the progress to the status bar.  In theory, you
  could create an ActiveX progress bar that would monitor and redisplay
  the progress from the status bar itself.  That's completely theoretical.
  I haven't looked into the logistics.  The key component would be whether
  the activeX would keep running on it's own, outside of the VBA thread.  

  Drew  

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
  Hindman
  Sent: Friday, April 03, 2009 2:21 PM
  To: Access Developers discussion and problem solving
  Subject: [AccessD] Queries Progress Bar

  Group

  ...I have the following in the FormLoad event:

      DoCmd.SetWarnings False
      tblExhibitorWebClear
      DoCmd.OpenQuery "qryExhByShowCurrentAppend"
      DoCmd.OpenQuery "qryExhByShowPastAppend"
      DoCmd.SetWarnings True

  ...that all happens in the background
  ...in the foreground I want to display a progress bar that shows the
  user 
  the progress during the entire process which can take a couple of
  minutes.
  ...don't want to use the vb ocx if possible, but would if necessary.
  ...don't want to use the progress meter in the status bar
  ...accuracy isn't necessary ...just a reasonable approximation.
  ...tried using a timer and labels but the queries always run before the 
  timer starts
  ...any ideas or samples much appreciated

  William