Solved

Delay or sleep function?

Posted on 2000-03-23
12
5,538 Views
Last Modified: 2010-10-05
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.
0
Comment
Question by:Deverill
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 1

Accepted Solution

by:
mb1ake earned 100 total points
ID: 2650982
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2651002
Public Sub Sleep(Seconds As Single)

     Dim Strt As Single
     Strt = Timer

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

End Function



0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2651015
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
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Expert Comment

by:pclement
ID: 2651050

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

0
 
LVL 1

Author Comment

by:Deverill
ID: 2653469
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?
0
 
LVL 1

Author Comment

by:Deverill
ID: 2653561
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.
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2653838
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
0
 
LVL 1

Expert Comment

by:pclement
ID: 2653864
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.



0
 
LVL 1

Author Comment

by:Deverill
ID: 2655033
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!
0
 
LVL 1

Expert Comment

by:pclement
ID: 2655708
Glad you found a solution, and thanks for sharing the points.
0
 
LVL 1

Expert Comment

by:mb1ake
ID: 2655921
I'm glad I could help.  Have a good weekend.

Michael
0
 
LVL 1

Expert Comment

by:HRISTeam
ID: 26001880
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question