Solved

Delay or sleep function?

Posted on 2000-03-23
12
5,527 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Allow user to edit an outgoing email 2 25
unable to create table-based data macro in MS ACCESS 2013 11 25
deduplicating based on criteria 2 21
Help with DoEvents 8 26
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

772 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