Copy a file in MS Access

Posted on 2006-04-18
Last Modified: 2011-08-18
what command code do I use to copy a file from one directory into another

Question by:michaeljusino1017
    LVL 84
    FileCopy(Source, Destination)

    Make sure to use full paths ...

    Author Comment

    this is what i have

    FileCopy (D:\Data\Lockout.txt, D:\Data\Lockout\Lockout.txt)

    I keep getting compile error because of the semicolons....what is the proper syntax
    LVL 84

    Accepted Solution

    Enclose the paths in double quotes and remove the parentheses:

    FileCopy "D:\Data\Lockout.txt", "D:\Data\Lockout\Lockout.txt"
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    There is a know problem using the MS-Access FileCopy. I have found this solution to be more reliable
    LVL 3

    Expert Comment

    There is another solution using Window Scripting Host. It includes option to overwrite existing file or not.

    Set oSh = CreateObject("Scripting.FilesystemObject")
    'Example: copy c:\oldFolder\Sample.mdb C:\newFolder\newSample.mdb true-overwrite.
    oSh.CopyFile "c:\oldFolder\Sample.mdb", "C:\newFolder\newSample.mdb", True
    Set oSh = Nothing

    Good Luck!!!
    LVL 26

    Expert Comment

    by:Alan Warren
    I too agree that using the file system object is the best option.
    Need to set a reference to "Microsoft Scripting Runtime Object Library"

    Lots of information and example usage here:
      Scripting Runtime Library  
      Working with Files

    LVL 84
    FWIW - I've had issues with FSO in distributed applications, and the use of the FSO is soundly thrashed in the VB Newsgroups about once a month (those guys REALLY hate it over there <g>).

    I've had some instances where the network admins turned Off scripting, or where the virus engines blocked it, and there can be some issues with versioning. To each his own, of course, but in this particular instance I see no real benefit in using the FSO for simple file creation. If you need more advanced file manipulations (like reading a file line-by-line) then it's definitely an option to consider, but there's always the APIs (for which FSO is simply a wrapper anyway).

    Kelvin's suggestion is interesting ... the only downfall I can see would be copying an incomplete file, since this code would copy an open file.
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    Yes, this can copy open files. I started using it some 5 years ago with Access 97 and have also used it with Access XP and 2003. The problem that occurred was that Access somethimes thinks a file is open when it is not and fails to copy it and creates an error (using FileCopy) - no rhyme nor reason. This code is essentially using the windows Copy File that you can use with Ctl C and Ctl V. If a file being open is an issue, then you need to look at the code prior to ensure a process has completed. It hasn't proved to be a problem for me. I have one database that calls it some 1500 times on newly created files over 4 days every month and has run without an issue for the last 4 years - a major financial reporting system.

    LVL 26

    Expert Comment

    by:Alan Warren
    Hi fellas,

    I haven't had any significant problems with using the FileSystemObject, been using it for years on many platforms. The usual precautions should be taken to ensure the source path is valid and the destination path exists or is created.

    Heres a simple example assigned to a button click event in an access form.

    Private Sub Command0_Click()
      ' Using the Scripting.FileSystemObject to copy files
      ' Requires a reference to Microsoft Scripting Runtime Library
      '   In any code window Menu > Tools > References
      '   Microsoft Scripting Runtime Library - Checked
      Dim strSourceFile As String
      Dim strDestinationPath As String
      Dim objFso As Scripting.FileSystemObject
      ' Set the target file path
      strSourceFile = "D:\Data\Lockout.txt"

      ' Set the target file path
      strDestinationPath = "D:\Data\Lockout\Lockout.txt"
      ' Instantiate instance of Scripting file system object
      Set objFso = New Scripting.FileSystemObject
      ' Check that the source file exists
      If objFso.FileExists(strSourceFile) Then
        ' Check that the destination folder exists.
        If objFso.FolderExists(Mid(strDestinationPath, 1, InStrRev(strDestinationPath, "\", , vbTextCompare))) Then
          ' Copy the file, overwriting existing file of same name...
          objFso.CopyFile strSourceFile, strDestinationPath, True
          ' maybe use objFso.Buildpath to create the folder
          ' depends how bulletproof you want to make this.
        End If
      End If
      ' Destroy instantiated objects
      Set objFso = Nothing

    End Sub

    Take care...

    Alan ":0)

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    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…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    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

    23 Experts available now in Live!

    Get 1:1 Help Now