How to you place a time delay in access VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey all,

For a macro I am running it is critical that there is a 3 second delay after
a particular step before continuing. In excel VBA there is a code to allow
you to wait a certain period of time, however it does not work in access, nor
can I find any code in the help that says how to do this. Any ideas?
 
Part of the macro saves an extremely large excel file after formatting it.
However, access does not want to wait for the file to finish saving before
continuing the rest of the macro, which causes errors because the rest of
said macro needs that saved file.

I got it figure out now, thanks guys =)
 
I think the main issue is that the file should be free right? In that case I
have written this piece of code for you that should solve the problem.
Pausing execution a set time is no guarantee the file is free. It takes as
parameteres the name of the file to wait on and the maximum time to wait in
seconds for it to free up. The function returns true when the file is free
and false if the file did not free up in the time specified.
I am not sure about the scope of the On Error Resume Next function so there
is a chance it will break custom errorhandling elsewhere in code. You would
have to try this yourself.

In that case I would use code like this:

Function ReturnOnFreeFile(sFileName As String, Optional nTimeOutSeconds As
Integer = 3) As Boolean
Dim nFileHandle As Integer
Dim dTimeStarted As Date

dTimeStarted = Now
ReturnOnFreeFile = False
On Error Resume Next
Do
nFileHandle = FreeFile
Open sFileName For Binary Access Read Write Lock Read Write As
nFileHandle
Close nFileHandle
' Bij een fout zal het document open staan
If Err.Number <> 0 Then
' Debug.Print "Error #" & str(Err.Number) & " - " &
Err.Description
Err.Clear
Else
ReturnOnFreeFile = True
Exit Do
End If
' Controleren of de lus al te lang loopt
If Second(Now - dTimeStarted) > nTimeOutSeconds Then Exit Do
' Control overgeven om andere events af te handelen
DoEvents
Loop
End Function
 
Back
Top