Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Backing Up Open MS Access Database

    Greetings all,

    I did a search, without success. I want to back an access database over the Internet using the CDO library. The CDO library seems to have no problem sending a copy of the database (about 7 megs). Problem: The actual database cannot be sent while it is open (I get an error message from within MS Access).

    The database is small, and single user. My concern is that the user, who is not terribly computer literate, will have difficulty backing it up. An easy solution is to make a utility access program (in effect an independent database) to do the backup when the database to be copied is closed. Still, it would be so much more elegant to sent a backup of the database from within itself!

    Any easy solutions?

    Regards

    John S
    Aylmer, Quebec, Canada

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What version of Access are we talking about? In 2007 (and I assume, thereafter) to backup an open database
    1. Click on the Office Button
    2. Bring cursor down to Manage
    3. Click on Back Up Database
    4. Click on Save

    The user can change the name to save the file to, between Steps 3 & 4, but the default is the original name plus the date, which is usually appropriate.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can copy all non-system tables of an Access database (if you work with a BE/FE model, that's all you need) using:
    Code:
    Sub BackupTables(ByVal DestinationFile As String)
    
        Const c_SQL As String = "SELECT * INTO [@T] IN '@D' FROM [@T];"
        Const c_Eval As String = "'@N' Not Like 'MSys*'"
        
        Dim obj As AccessObject
        Dim dbs As DAO.Database
        
        If Len(Dir(DestinationFile)) > 0 Then Kill DestinationFile
        Set dbs = DBEngine.CreateDatabase(DestinationFile, dbLangGeneral)
        dbs.Close
        For Each obj In Application.CurrentData.AllTables
            If Eval(Replace(c_Eval, "@N", obj.Name)) Then
                CurrentDb.Execute Replace(Replace(c_SQL, "@T", obj.Name), "@D", DestinationFile)
            End If
        Next obj
        
    End Sub
    Example:
    Code:
    BackupTables "U:\MyDbBackup_20130420.mdb
    It should not be very difficult to copy the created file (DestinationFile) over a network.
    Have a nice day!

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Greetings all,

    Thanks for the suggestions. I suspect I have a paradigm problem; there are smarter solutions than sending a backup over the Internet using the CDO library! Perhaps I should just make up a simple backup utility in DOS to copy to an independent device.

    By way of giving back, sending the database (all 6.7 megs) as an attachment over the internet worked, BUT seemed to cause some memory issues and froze the program (perhaps because I hit the send button a few times!). There may be practical limits on attachment size when using the CDO library.

    Regards

    John S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •