1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2003 macro to backup database

Discussion in 'Business Applications' started by karlhaywood, Jul 18, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Hi, is it possible to have a macro that backs up my database, and a button on my admin menu which is clicked and then runs the macro.

    Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Karl, the best method I have found for "Backing Up" databases uses a seperate database which runs some simple VBA to copy the database in to one of seven folders. There is one folder for each day of the week and it puts it in the folder "of the day" based on the day's date.
    But I use Windows Scheduler to open the database which then runs the code.

    This method ensures that you have more than one backup, which are just one day or less apart, this can avoid the problem of backing up a database that has just developed a problem.
    One thing you can't do is copy an Open database, although you can overwrite it with a Compacted & Repaired version.
     
  3. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    How would i go about doing that then?
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    First of all in the Folder that you want to keep the Backups create 7 Folders one for each day of the week i.e Monday, Tuesday, Wednesday etc

    Then copy this database in to your main folder.
    Note you need to change these 2 lines of Code
    SourceFile = "C:\Access\Barcode.mdb" ' Define source file name.
    DestinationFile = "C:\Access\" & Format(Now(), "dddd") & "\Barcode.mdb"

    To have the full path to and the name of your database.

    You can of course Backup more than one database at the same time by repeating the code.

    You can also change the code to put all the Copies in one Folder and add the Date elements to the Database Name.
    For instance in my case Barcode 18-07-2010.mdb instead of Barcode.mdb
    If you open the attached database in the normal way it will run the code and cause an error, because it is looking for my Database

    All you have to do once you have the database creating the desired backup is to use Microsoft Scheduler to create a scheduled time to open the database, On Startup is a good time.
     

    Attached Files:

  5. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    What do you mean by the main folder? Is that the folder that contains monday, tuesday etc?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Karl, yes, although you can put it anywhere really as long as you know where to find it to set up MS Scheduler.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/936539

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice