Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delay or sleep function?

Posted on 2000-03-23
12
Medium Priority
?
5,623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 1

Accepted Solution

by:
mb1ake earned 400 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

610 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