Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Copy a file in MS Access

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

Question by:michaeljusino1017
  • 3
  • 2
  • 2
  • +2
LVL 85
ID: 16483120
FileCopy(Source, Destination)

Make sure to use full paths ...

Author Comment

ID: 16483155
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 16483174
Enclose the paths in double quotes and remove the parentheses:

FileCopy "D:\Data\Lockout.txt", "D:\Data\Lockout\Lockout.txt"
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 22

Expert Comment

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

Expert Comment

ID: 16484810
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
ID: 16485876
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 85
ID: 16486216
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
ID: 16486603
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
ID: 16486829
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 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