Prevent filecopy from overwriting existing file in access vba

Posted on 2012-09-04
Last Modified: 2012-09-04
Here is the module I'm using;

Option Compare Database

' Procedure : CopyFile
' Author    : CARDA Consultants Inc.
' Website   :
' Purpose   : Copy a file
'             Overwrites existing copy without prompting
'             Cannot copy locked files (currently in use)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strSource - Path/Name of the file to be copied
' strDest - Path/Name for copying the file to
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' 1         2007-Apr-01             Initial Release
Function CopyFile(strSource As String, strDest As String) As Boolean
On Error GoTo CopyFile_Error
    FileCopy strSource, strDest
    CopyFile = False
    Exit Function
    If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & strSource & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               Err.Number & vbCrLf & "Error Source: ModExtFiles / CopyFile" & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    End If
    Exit Function
End Function

Open in new window

Then I call the file to copy during a command on click event as so:

Call FileCopy("C:\Users\jmeyer\Desktop\New folder\Blank.jpg", "\\sfile0\e\data\jmeyer\Jarred\iPadDocs\" & Me.KeyField & ".jpg")

The problem is, if the file already exists it will just replace it with the blank where I would like it to just do nothing. In other words if the file exists then do nothing else copy the blank file and save it as so.

Thanks for the advice!
Question by:SeyerIT
    LVL 61

    Accepted Solution

    You can check for an existing file like this:

    If Dir("YourFilePath") & "" = ""  Then
         Call FileCopy...
       ' Do nothing
    End if
    LVL 2

    Author Comment

    Perfect! Thanks!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now