Results 1 to 3 of 3

VBA to create dated backup file

  1. #1
    phbryan is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    4

    VBA to create dated backup file

    Hello,

    This if my first time on this forum. I am usually on an excel VBA forum. I have code in an excel workbook that i want to add to that will open an access database file, and do a save as command giving it a date stamped name. for example, "database 03-30-2015" and save it in a filepath of my choosing.

    Any ideas?


    Thanks

  2. #2
    phbryan is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    4
    I came up with this code:
    Code:
    FileCopy "S:\Shipping Receiving\Extrusion\dbExtrusion Receiving.mdb", "S:\AC 174 Railing Certification Program\Database Backups\Backup of dbExtrusion Receiving " & Month(Now()) & "-" & Day(Now()) & "-" & Year(Now()) & ".mdb"
    but i get a permission denied error. Ive read that this happens when the file is open by other users. I can get around the file being open by other users, is there a different code i can use?

  3. #3
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Access will create an .LDB file with the same name if anyone is using (and locking) the .MDB file. One thing you can do is check for the presence of an .LDB before attempting the copy. If it exists, then alert the user that they need to wait and try it again later.

    Another option is to call an outside .BAT/.CMD file that performs the file copy from Windows. The risk here is that you could attempt to copy the database while another user is editing data. This could potentially cause database corruption in both copies which you definitely DON'T want.

    At work, I have a script that is automatically run after-hours to backup the database. And even then, it checks for an .LDB file first to make sure nobody left the database open when they left.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create a Macro to run backup of database
    By Harley Guy in forum Macros
    Replies: 7
    Last Post: 10-02-2014, 12:33 PM
  2. Schedule a Future Dated Transaction
    By burrina in forum Forms
    Replies: 21
    Last Post: 12-27-2012, 06:16 PM
  3. Replies: 1
    Last Post: 04-25-2012, 12:36 PM
  4. SQL Server Differential backup in a separate file
    By buienxg in forum SQL Server
    Replies: 2
    Last Post: 12-17-2011, 04:41 PM
  5. Automatically createing Backup.mdb file
    By nareshk in forum Access
    Replies: 3
    Last Post: 11-16-2009, 12:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums