'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



Copying a File

One of my clients requires the ability to make a local copy of their data each morning. One of the staff works an early shift starting at 7 am. They start up the database, and on their version of the Main Menu there is an extra button labelled "Make Snapshot". They click this and Access makes a copy of their backend.

The name of their back end file is "RUSFinBE.mdb". If they click that button on May 20, 2005 at 7:23, the new copy is automatically named "RUSFinBE 2005-05-20 07-23.mdb". Here is the code that does this task.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

(article continues after sponsor spot)

Private Sub CreateSnapshot()

' PURPOSE: Copy a file on disk from one location to another.
Dim CopyString As String
Dim SourceFile As String
Dim DestFile As String
Dim TimeStamp As String

TimeStamp = Format(Now(), " yyyy-mm-dd hh-nn")
SourceFile = Chr(34) & "C:\aFolder\aFile" & ".mdb" & Chr(34)
DestFile = Chr(34) & "C:\aFolder\aFile " & TimeStamp & ".mdb" & Chr(34)
CopyString = "CMD.EXE /C COPY " & SourceFile & " " & DestFile
Call Shell(CopyString, vbNormalFocus)
MsgBox "when copying is done, new filename will be " & DestFile

End Sub

The Chr(34) creates a quote symbol, which is needed since there are blank characters in the file name. Their system obviously has a real base file name in place of "C:\aFolder\aFile". I will leave it to the reader to either hard code the name of their back end file, or create some "smarts" that determines the actual name. (Hint currentproject.path, etc)

I trust that the above code is self explanatory.

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.


© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy