application.wait

[B. Meincke]

I am running Office 2000 Pro on a domain's worth of XP Pro workstations.

Can someone please help me understand why 'application.wait' works in Excel
VBA code but not in the code of an Access form?

I thought perhaps it might be an object library issue, but I have duplicated
those in the Excel workbook exactly in my Access form and still it fails,
reporting...

"Compile error: Method or data member not found"

Any suggestions or advice would be greatly appreciated. Thanks in advance.

 

[Allen Browne]

The VBA code in Access is not the same as in Excel or Word.

You can easily enough create a Wait function:

Public Function Wait(lngSeconds As Long)
Static bRunning As Boolean
Dim dtEnd As Date

If Not bRunning Then
bRunning = True
dtEnd = DateAdd("s", lngSeconds, Now())
Do Until Now() >= dtEnd
DoEvents
Loop
End If
bRunning = False
End Function

If you need to handle fractions of a second, use this one:
http://www.mvps.org/access/api/api0021.htm

 

[Klatuu]

There is no Wait method in Access.
You could write a Do Loop to continually check the current time.

What, exactly, are you trying to do? Maybe if you tell us what you want to
do, we can come up with a way how to do it.

 

[Dirk Goldgar]

in message

I am running Office 2000 Pro on a domain's worth of XP Pro workstations.

Can someone please help me understand why 'application.wait' works in
Excel
VBA code but not in the code of an Access form?

I thought perhaps it might be an object library issue, but I have
duplicated
those in the Excel workbook exactly in my Access form and still it fails,
reporting...

"Compile error: Method or data member not found"

Any suggestions or advice would be greatly appreciated. Thanks in advance.

Each application exposes its own set of objects and methods. VBA, as a
language, provides the basic programming structure for working with those
objects and methods, as well as a few object types of its own, but the bulk
of the objects and methods available depend on which application is hosting
the running code.

"Wait" is a method of the Excel Application object. The Access Application
object doesn't provide that method. You can simulate it by calling the
Windows API Sleep function, as shown on this web page:

http://www.mvps.org/access/api/api0021.htm
API: Make code go to Sleep

Note that the API function waits a specified number of milliseconds, rather
than waiting until a specified time of day as the Excel Wait method does.

 

[B. Meincke]

Thank you all for your speedy replies!

Everyone's information is great. I think I am beginning to understand.

Fair enough, Klatuu:
What I am trying to do is have the click event of a form's command button
use XP's built in compression program to zip our database back end and then
update it according to a series of text imports. The trouble is getting the
sub to pause while the compression takes place and have it continue only when
the compression is complete.

I didn't include the code, as I have been playing with it for a week and it
is now a complete jumbled mess. Butchered, actually.

From an earlier post, I have been kindly directed to the following site:
http://www.mvps.org/access/api/api0004.htm

But, I'm afraid my inexperience does not allow me to see how to call the
module from the form command button sub. I'm not sure what Terry means by,
"and use the Shell sub."

Again, thank you all.

 

[B. Meincke]

Okay, here goes...

Except for the 'application.wait' failing and, without it, the sub
continuing before the archive is complete, the following code seems to work:


Dim fs, f, s
Dim DefPath As String

DefPath = CurrentProject.Path
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

If FileOrDirExists(DefPath & "AttdB_BE.ldb") = True Then

MsgBox "The database is in use, please try again later.", vbOKOnly,
"Sorry..."
mcrQuit

Else

Set f = Nothing
Set fs = Nothing

DefPath = CurrentProject.Path
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

strDate = Format(Now, "yyyy-mm-dd")
sZipFile = DefPath & "BE Backups\AttdB_BE " & strDate & " (before).zip"

sFile = DefPath & "Attdb_BE.mdb"

'Create empty Zip File
NewZip (sZipFile)

Set oApp = CreateObject("Shell.Application")

oApp.Namespace(sZipFile).CopyHere sFile

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(sZipFile).Items.Count = 1
'Application.Wait (Now + TimeValue("0:00:01"))
Loop

Set oApp = Nothing

MsgBox "Run the update scripts...", vbOKOnly, "Simulate:"

End If

End Sub

 

[David W. Fenton]

What I am trying to do is have the click event of a form's command
button use XP's built in compression program to zip our database
back end and then update it according to a series of text imports.
The trouble is getting the sub to pause while the compression
takes place and have it continue only when the compression is
complete.

Then WAIT is *never* going to be a good idea in all cases (think
slow computers).

What you need is an COM-based method for launching the file
compression. Then the even will either occur synchronously (i.e.,
the code will not continue the compress command completes), or your
COM interface will provide a property that tells you when the
compression process is complete.

 

[B. Meincke]

Thanks Allen. Is there any way I could revise the criteria for the sub
continuing to be when the Windows' based compression utility was finished
with zipping the back end as opposed to a static time period?

Thanks for your continued support in keeping my head above water in all
this!!!
--
BJM
ACE Assistant
Gary Allan High School

The VBA code in Access is not the same as in Excel or Word.

You can easily enough create a Wait function:

Public Function Wait(lngSeconds As Long)
Static bRunning As Boolean
Dim dtEnd As Date

If Not bRunning Then
bRunning = True
dtEnd = DateAdd("s", lngSeconds, Now())
Do Until Now() >= dtEnd
DoEvents
Loop
End If
bRunning = False
End Function

If you need to handle fractions of a second, use this one:
http://www.mvps.org/access/api/api0021.htm

 

[B. Meincke]

Thank you for taking the time to reply, David.

I believe I understand the concept but I'm afraid I have no idea how to
implement what you are describing.

I was hoping to keep things simple enough to be withstood by my
inexperience! <G>

 

[Allen Browne]

Thanks Allen. Is there any way I could revise the criteria for the sub
continuing to be when the Windows' based compression utility was finished
with zipping the back end as opposed to a static time period?

So you want to wait until another process finishes? See:
Shell and Wait
at:
http://www.mvps.org/access/api/api0004.htm

 

[B. Meincke]

Oh dear. Thanks again, Allen, but I have been playing with the sub on this
link for quite some time but can't seem to code a call to it that looks to
see if the WXP compression utility is finished with its task. The sub
requires a path to the app from what I can decifer and I'm afraid I can't
seem to find what that would be.