Link to home
Start Free TrialLog in
Avatar of Deverill
Deverill

asked on

Delay or sleep function?

What is the best way to pause in Access VBA?  There are two goals here.
  1.  Sleep for x seconds (without hogging the resources).  [30 pts]
  2.  Use the Shell command to do a task such as copy a large file and do nothing until that finishes. [70 pts]

I have the problem of my program using a copy before DOS actually finishes the copy process.  I can wait 10 seconds if necessary but would rather just wait until it finishes before going on just in case the computer is a slow one or to take advantage of it finishing more quickly.

Full points to good answers to #1 and #2, partial points to just one or the other.
ASKER CERTIFIED SOLUTION
Avatar of mb1ake
mb1ake

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BrianWren
BrianWren

Public Sub Sleep(Seconds As Single)

     Dim Strt As Single
     Strt = Timer

     Do
          If Timer >= Strt + Seconds Then Exit Do
          DoEvents
     Loop

End Function



Why are you using a shell command to do the copying?  FileCopy will copy files, and it will halt Access code till it's done.

If you can give more details, more help can be given.

You could use AppActivate with Wait, but I don't know if the app you are shelling to can receive the focus, or whether you would want it to . . .

Brian

My personal favorite is the SHFileOperation API function call as it will also display the standard Windows file operation dialogs. You don't need to do anything to pause your app as the function call is synchronous.

http://www.mvps.org/vbnet/code/shell/shfileopadv.htm

Avatar of Deverill

ASKER

mb1ake and pclement:
  Thanks for the links.  Good info!

BrianWren and others:
  I have a 3rd party application based on Access and custom VB front ends (which I do not have access to the source) that handles sales in a retail store business.  The managers have a program that will let them access this data.

  Due to some poor security modeling, only managers can print price tags.  This is unacceptable since managers can also do voids and theft is an issue.
 
  My form has a button that:
    1. Shells to Dos to copy the database.
    2. Loops through the users deleting all of them.
    3. Adds a "fake" user with manager privs so anyone can print tags.
    4. Using Shell to launch the manager program so when they exit they are back in my program (which does reporting, etc.)
  And no, I don't want to create a new tag-printing routine from Access <grin>.

  I did not know about the FileCopy statement (shoulda known... VBA/Access can do most anything itself).

Questions:  If I put in a DoEvents will it sufficiently allow the system to continue functioning moderately well?  I don't want to seriously hinder performance on it since the registers are also using the database on the same machine.

I have an answer to my original question but will keep it open a bit longer to see if there is a better way to do things.  I will up the points and split them out... I got several good answers.  If I ask customer services to split the points then are they swamped and it will take forever or should I just post "Points for" questions for each person?
More information:

I just did an experiment with FileCopy.  If the file is open it will return an error of "Permission Denied".  

In our environmend the file will always be open but the information lost by copying a changing file is sales transactions which is not important for this application.

The good thing (in this case) about DOS is that it will copy the file even if it is open.
It sounds like your process can wait for the other program to quit, so AppActivate with 'Wait' seems like the candidate that should get your vote, (so to speak).

Brian
I don't see where DoEvents will help you under these scenarios. The suggestions, including FileCopy operate synchronously so essentially your application is waiting for the function to complete until subsequent code will execute.

I personally do not use FileCopy, since I've had reports of its unreliability when using UNC pathnames and open files. And as BrianWren mentioned I really see no reason to use Shell since there are more effective and elegant methods to do this from Windows code.

If you really want full control (which requires more work of course) you can use the CopyFile API function call as well:

http://www.mvps.org/vbnet/code/fileapi/filebackup.htm

But for basic trouble-free file copying I still prefer SHFileOperation.



Ok guys, here is how I am doing the points.

To mb1ake - 100 pts.
   I actually implemented the solution in the link Michael provided and it works great.  Thanks!

To BrianWren - 75 pts.
  You gave me the sleep proc and clued me into FileCopy (even though it didn't work in this application it will in others).  Also the AppActivate was something I tried at first but it just never worked for me (my fault prob.)  Thanks for taking the time to ask for more info trying to help.  Look for another Q with your points.

To pclement - 75 pts.
  The SHFileOperation API and CopyFile API pointers are great and I will probably use them later but the GetExitCode was fast and dirty and it was what I needed.  Look for another Q with your points.

To all, thanks for the help.  I had a difficult time figuring points but thought this would be fair... I have more points than I have used lately so why not spread them around.  The answers were helpful so it is not like I am throwing them away.

Especially thanks for the pointers to mvps.org I will be going back there!
Glad you found a solution, and thanks for sharing the points.
I'm glad I could help.  Have a good weekend.

Michael