Capturing FileCopy fails in Access Code

Buck_Beasom
Buck_Beasom used Ask the Experts™
on
I am using the File Copy to deploy upgrades to an application, and I am trying to catch instances where the copy fails because the User has the application open. I am trying to use this code to capture the failure:

FileCopy strFileCopyFrom, strFileCopyTo
If Err <> 0 Then

The Err is not capturing the error. I am resetting it to 0 before each copy attempt.

Is there a better method for catching the instances where the Copy fails?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What is your On Error statement?

 Should be On Error Resume Next

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
i.e.

On Error Resume Next
Err = 0

FileCopy strFileCopyFrom, strFileCopyTo

If Err <> 0 Then
John TsioumprisSoftware & Systems Engineer

Commented:
When do you perform this copy...
The simple way is to copy the file when the user logins...
If in the mid day you need that you probably need to check Windows Process  to see if Ms Access is active ..like here : http://www.wisesoft.co.uk/scripts/vbscript_check_if_a_process_is_running.aspx
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

I believe its because you should be using:
If Err.Number <> 0 Then

Open in new window

Buck_BeasomDatabase Designer

Author

Commented:
Thanks to all.

In no particular order:

I have successfully used just the "If Err <> 0" in other cases, notably when a User generated process tried to save over an Excel file that is already open.

I AM using the On Err Resume Next. (Actually, Jim, I think I may have gotten the code from you some time back!) And I am setting Err to Zero before each attempt so I don't inherit a previous fail.

The problem seems to be that if the copy fails, it is not changing Err from 0 to another value. I actually set up the first copy effort to a file I KNEW the user had open so I could throw the error and capture it. But when I walk through the code line by line and the File Copy runs but fails, the Err is remaining = 0 so I can't capture it.

Since "File Copy" is really just executing a DOS command (remember those days!) I'm afraid that - unlike an error that would occur inside the Access environment (like a run time error) - the failure of the File Copy command doesn't throw a value into the Err variable.

I can't use the approach of copying the new app down on login (which VB does - or did when I was using it) automatically because the app being copied is the one where the Login occurs. So - unless there is something I don't know about - the copy would ALWAYS fail because the file would be open when the File Copy command was called. So at the moment I am stuck with having to examine EACH destination folder to see if the new version copied over. I'm wondering if maybe there is some way to display each destination folder's contents - with the Date Stamp - so I could see any that were not the current version. A little klugy - but better than nothing.
John TsioumprisSoftware & Systems Engineer

Commented:
you can use a simple bat file for the copy on login
just xcopy source destination
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You should study my article on exactly this subject:

Deploy and update a Microsoft Access application with one click

It demonstrates how to use a shortcut and a script to avoid all the troubles like the one you are facing.
Buck_BeasomDatabase Designer

Author

Commented:
Gustav. I am going to dig into what you provided. I'm pretty sure it will come in useful in a number of settings. Will get back to you as soon as I have completed my study. Thank you!
You could also look into using the FileSystemObject for your copy needs. It should have better error feedback:
FileSystemObject CopyFile
Database Designer
Commented:
Well, there were a number of excellent suggestions, particularly Gustav Brocks. Unfortunately, this is all working in the Citrix environment and for security reasons we are not allowed to create shortcuts! (Dumb, I know.)

Since all I was trying to do is validate that every user who is supposed to get an application upgrade got it, I came up with this solution:

strMSG = ""

    Do Until rsUserList.EOF
        rsFileCopyList.MoveFirst
        Do Until rsFileCopyList.EOF
            strFileCopyFrom = rsFileCopyList!Citrix_File
            If Right(strFileCopyFrom, 5) = "accdb" Then
                If Len(strMSG) = 0 Then
                    strMSG = strFileCopyFrom & " " & FileDateTime(strFileCopyFrom) & Chr(10)
                End If
            End If
            strFileCopyTo = "Q:\Users\" & rsUserList!Citrix_User_Folder & "\" & rsFileCopyList!Deployment_Path & "\" & rsFileCopyList!File_Name
            Me.lblBanner.Caption = "Copying: " & strFileCopyFrom & " to " & rsUserList!Citrix_User_Folder
            Me.Repaint
            On Error Resume Next
            FileCopy strFileCopyFrom, strFileCopyTo
            If Right(strFileCopyTo, 5) = "accdb" Then
                strMSG = strMSG & strFileCopyTo & " " & FileDateTime(strFileCopyTo) & Chr(10)
            End If
            rsFileCopyList.MoveNext
        Loop
        rsUserList.MoveNext
    Loop

MsgBox (strMSG), vbInformation, "Deployment Details"

All this requires is examination of the message to make sure that all of the date/time stamps agree with the top one. This wouldn't be all that practical for huge user populations, but for the ones I am dealing with it gets the job done.

Thanks to all for your contributions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial