code to have subroutine wait doesn't work

[B. Meincke]

Hi all,
I have the following code to allow a zip operation to finish before moving
on in an Access 2000 form command button on_click event:

On Error Resume Next
Do Until oApp.Namespace(sZipFile).Items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop

This fails, flagging the word 'wait' with the following error reported:

Compile error:
Method or data member not found

Is this because there is an object library missing? If so, which? If not,
what else am I missing?

Thanks in advance for any advice or suggestions.

 

[Bill]

If you don't find a satisfactory answer or otherwise solve
your problem, Terry Kreft wrote an application entitled
ShellWait that I've used for several years to do what
you're wanting to accomplish.

See: http://www.mvps.org/access/api/api0004.htm

Bill

 

[B. Meincke]

Thank you, Bill, for your response. Actually, I have seen the application you
recommended. I understand how to create the new module, of course, but I am
not sure how to call it in the form's command button code --- ("...and use
the Shell sub.") What does Terry mean by, 'Shell sub'? My inexperience
showing again, I guess.

I would, however, for the sake of my own learning process, still like to
know why my original code fails.

Again, thanks.
--
BJM
ACE Assistant
Gary Allan High School

If you don't find a satisfactory answer or otherwise solve
your problem, Terry Kreft wrote an application entitled
ShellWait that I've used for several years to do what
you're wanting to accomplish.

See: http://www.mvps.org/access/api/api0004.htm

Bill

 

[Bill]

Here's a simple function (stripped) that uses Winzip to zip a file.
With ShellWait, the function essentially awaits completion
of the operation before proceeding with the execution of
the ensuing code.

Public Function ZipTMSData()
Dim WinZip As String
Dim ZipFile As String
Dim ShellString As Variant

'=======================================
' IPPath is the path taken from the application's installation
' property table. Winzip code is found from its default
' location.
'=======================================
WinZip = Left(IPPath, 2) & "\Program Files\Winzip\wzzip.exe"
ZipFile = IPPath & "\TMS Data.zip"
ShellString = WinZip & " " & ZipFile & " """ & IPDatabase & """"

ShellWait (ShellString)

End Function

Bill

Thank you, Bill, for your response. Actually, I have seen the application
you
recommended. I understand how to create the new module, of course, but I
am
not sure how to call it in the form's command button code --- ("...and use
the Shell sub.") What does Terry mean by, 'Shell sub'? My inexperience
showing again, I guess.

I would, however, for the sake of my own learning process, still like to
know why my original code fails.

Again, thanks.

 

[B. Meincke]

Bill,
That's awesome. It may be stripped but so much easier for this novice VBAer
to understand!

Thank you so much. However, if I might indulge one more question...

Not all workstations in our domain have WinZip but all are XP so have the
built-in compression utility. Can you help me revise your code to utilize it
instead of WinZip?

Again, thank you.

 

[B. Meincke]

I'm afraid I don't understand why application.wait works in Excel and not in
Access. Can anyone clear this up for me?

Thanks.

 

[Bill]

First, look at that portion of the thread beginning with May 6, 2008
at: http://www.neowin.net/forum/lofiversion/index.php/t489301.html

Then, look at:
http://www.7-zip.org/download.html

I think that with the impending loss of XP support, you would be
wise to follow the suggestion in the "neowin.net" thread and adopt
the external zip application. I don't personally know just how bad,
if at all, the XP built-in compression utility might be. I've used
Winzip so long that I've not thought much about it for some time.
(Winzip is great for scheduled backups and the zip files it creates
can be un-zipped by XP without having to have Winzip to recover.)

Bill