Wait for Copyfolder to end

[Bill]

In the sub below, I need something after the
"copyfolder" that will cause the code to wait
until the copy is completed or otherwise raises
an error condition. In the past I've used
ShellWait to invoke exe's outside of VBA,
but I don't see how that would apply to this
case.

Any suggestions?

Thanks,
Bill

=======================================
Private Sub cmdBkupDB_Click()
Dim fs As Object
On Error GoTo ErrHandler

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFolder "c:\Recipies", "d:\Recipies"

Me.tbRecipeName.SetFocus
MsgBox "Recipies database backed up successfully"

ExitHandler:
Set fs = Nothing
Exit Sub

ErrHandler:
MsgBox "Error Encountered while backing up Recipies database: " &
vbNewLine & _
"Error number = " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler

End Sub
======================================

 

[Jeanette Cunningham]

Bill,
here is some generic code to copy using VBA from within Access.
Dim SourceFile, DestinationFile
SourceFile = "SRCFILE" ' Define source file name.
DestinationFile = "DESTFILE" ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.

You won't need to worry about waiting until the copy finishes.

Jeanette Cunningham

 

[Bill]

FileCopy generates an error if the file is open.
In this case, there are a couple of files within
the folder that are open, so the FileCopy
method isn't applicable.

Thanks anyway,

Bill

 

[Douglas J. Steele]

If there are files that are open, it may not be appropriate to copy them.

Just because FSO lets you copy open files doesn't mean that it's appropriate
to do so! (The copied file may be in an inconsistent state)

 

[Bill]

The file that is open is the backend DB. I would think that
while the code is executing that DB activity would be
quiesed?

Bill

 

[Douglas J. Steele]

The file that is open is the backend DB. I would think that
while the code is executing that DB activity would be
quiesed?

It's definitely not a good idea to copy an open database. You really
shouldn't make any assumptions as to whether or not Access is doing anything
at any point the database is open.

 

[Bill]

Thanks Doug.

I understand what you're saying. Is there a way I can
close the back end, wait for the copy to end and then
re-open the back end database?

The form, that contains the invoking command, has as
its RecordSource a query based on tables in the back
end.

Bill

 

[Douglas J. Steele]

If the form that's trying to do the copy has a connection open to the
back-end, there's nothing you can do.

What you need to do is have an unbound form (to ensure that there are no
connections) and make sure the unbound form is the only form open (best
thing to do is ensure that the ldb file doesn't exists in the folder). Then,
and only then, are you safe to do the copy.

 

[Bill]

Thanks Doug, I'll take a slightly different approach
assuring that there no open bound forms and that the
ldb file is excluded from any copy operations.

Just to be sure, what's the statement that would
reveal "how many" forms there are in the currently
opened collection?

Thanks again,
Bill

 

[Douglas J. Steele]

Forms.Count

 

[Bill]

Thanks, that ought to do the trick for this caper.
Bill