How to make a microsoft Access macro pause 2

[HalfBased]

Im creating a maco in Access that executes a batch file which executes a script that generates several txt files that are then imported into the database by the next action in the macro. How can I pause between the creation of the txt files and the transfer of said files for a minute so that the txt files will actually be there when the next part of the macro tries to import them?

 

[CharlesFS]

Hi, to make a pause:

create the sub:
Sub sleep(tSecs As Single)
' Timer to create a pause
Dim sngSec As Single

sngSec = Timer + tSecs
Do While Timer < sngSec
DoEvents
Loop
End Sub

to pause 2 seconds your code use: Sleep 2
like this:

sub yoursub()

sleep 2 'this make a pause for 2 seconds to increse or decrease only change the "2" value

End sub


sorry my english

 

[PHV]

First, don't use use macro but VBA (for better error handling)
Second, how do you executes a batch file from within access ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HalfBased]

Thanks Charles! Im brand new to VBA and Im not quite sure how to create the sub. I can cut and paste like a champ but Im not sure what I should and should not paste in a module. When I try to add the line Runcode in the macro I get error messages with the code as it is. Any chance you can dumb down your response for me? thanks

 

[HalfBased]

PHV,

A batch file is an MS DOS application which will run in access. it took a bit of "remodeling" but i figured out that i had to change directories within the batch file. also i had to rename it with a .bat extention instead of .cmd and take out the spaces in the batchfile name. I basically changed the line from:

s:WC CSO PI - Local\Phones\Scripts\08 Tables\New Tables\
Start Tables Export -1.acsauto

to:

s:
cd \WC CSO PI - Local\Phones\Scripts\08 Tables\New Tables\
Start TablesExport-1.acsauto

I mean its just a simple batchfile that starts a script in AVAYA. I couldnt make a macro to run the script itself because its not a ms windows or ms dos application. I just jumped into DB work before I got my present job and I didnt know how to actually write in VBA. so I tried a little work around and....... it worked. go me

 

[CharlesFS]

Hey, post the .bat file script here, perhaps i transform in vb code to you.

 

[HalfBased]

CharlesFS,

It's actually three lines; The first to change the drive letter; the second to change the directory and then the third to execute a script. If I dont do it that way the batchfile doesnt work. Ok So here it is.......

s:
cd \WC CSO PI - Local\Phones\Scripts\08 Tables\New Tables\
Start TablesExport-1.acsauto

Thanks for your help, Chuck!

 

[CharlesFS]

Hi, this vba code execute .bat file and wait it close to start new commands. To use you need to copy the code and past in a new module.
Code:


Option Explicit

'//The Shell function runs other programs asynchronously so what
'//What you basically have to do is Open the existing Process
'//for the running Application and, LOOP & WAIT for the processes return state
'//ie when the specified process is in the signaled state
'//or a timeout occurs.

Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Private Declare Function WaitForSingleObject Lib "kernel32" ( _
ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, _
lpExitCode As Long) As Long

'&HFFFF
Private Const SYNCHRONIZE = &H100000
'// Note:SYNCHRONIZE Windows NT/2000
Private Const INFINITE = &HFFFF 'OR -1&
'// INFINITE, the function’s time-out interval never elapses.
Private Const STILL_ACTIVE = &H103

Public Function ShellAndWait(ByVal BatFile As String)
'
' Shells a new process and waits for it to complete.
' Calling application is totally non-responsive while
' new process executes.
'
Dim PID As Long
Dim hProcess As Long
Dim nRet As Long

'// Unlike other Functions Shell generates an error
'// instead of returning a 0 so handling the error
'// = Application NOT started.
On Error Resume Next
PID = Shell(BatFile, vbMinimizedNoFocus)
If Err Then
'// handle the error here and End
MsgBox "Could NOT exercute:= " & BatFile
End
End If
On Error GoTo 0

'// SYNCHRONIZE For Windows NT/2000:
'// Enables using the process handle in any of the wait
'// functions to wait for the process to terminate.
'// obviously with NT you need access rights.
hProcess = OpenProcess(SYNCHRONIZE, False, PID)
'// Just set the dwMilliseconds to INFINITE to initiate a Loop
nRet = WaitForSingleObject(hProcess, INFINITE)

Do
GetExitCodeProcess hProcess, nRet
DoEvents
Loop While nRet = STILL_ACTIVE

CloseHandle hProcess

End Function



Sub HalfBasedRoutine()
Dim sApp As String

'// Define the Application FullPath here
sApp = "c:\Scripts.bat"

ShellAndWait sApp 'Wait .bat terminate

'insert here what you want to make after .bat execute.

End Sub

 

[CharlesFS]

Ah, in access 2007 you have a function to convert the macro into a vb comand. You can convert your access macro and insert de converted code into the :

Sub HalfBasedRoutine()
Dim sApp As String
'// Define the Application FullPath here
sApp = "c:\Scripts.bat"
ShellAndWait sApp 'Wait .bat terminate

'CONVERTED MACRO CODE

End Sub

 

[PHV]

CharlesFS, here a simpler way:
CreateObject("WScript.Shell").Run sApp, , True 'Wait .bat terminate

 

[CharlesFS]

PHV
wO

http://Wwwwww,

very very very simple! =)

 

[Christineeve]

Dear CharlesFS
Using your simple Sleep API completely helped me resolve my email sending problem.

The emails would send too fast and Outlook would throw a message box to prompt to send the next email. This is an application that has to run over night unmonitored.

By adding a 10 second sleep (10000), it gave the subroutine time to send out the email and not throw the dialog.

Thank you very much!