Solved

Delay or sleep function?

Posted on 2000-03-23
12
5,484 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
Comment Utility
0
 
LVL 9

Expert Comment

by:BrianWren
Comment Utility
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
Comment Utility
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
 
LVL 1

Expert Comment

by:pclement
Comment Utility

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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:BrianWren
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad you found a solution, and thanks for sharing the points.
0
 
LVL 1

Expert Comment

by:mb1ake
Comment Utility
I'm glad I could help.  Have a good weekend.

Michael
0
 
LVL 1

Expert Comment

by:HRISTeam
Comment Utility
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now