Copy a file in MS Access

what command code do I use to copy a file from one directory into another

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
FileCopy(Source, Destination)

Make sure to use full paths ...
michaeljusino1017Author Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Enclose the paths in double quotes and remove the parentheses:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kelvin SparksCommented:
There is a know problem using the MS-Access FileCopy. I have found this solution to be more reliable
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!!!
Alan WarrenApplications DeveloperCommented:
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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Kelvin SparksCommented:
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.

Alan WarrenApplications DeveloperCommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.