Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Compact / Backup (XP)

    Hi,

    I working on a database that I'd like to provide the user with a command button to backup the database tables. Is this possible?

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compact / Backup (XP)

    Leesa

    Does Access XP have a Backup command available under Tools..Database Utilities?

    My copy of 2003 does, but I don't have XP.


    2003 recognises the command docmd.runcommand acCmdBackup

    but whenever I try to use it I get the error that the command backup is not available now. I can't find out any more about it in help, or anywhere else.

    I am wondering if this might provide a very simple solution to this problem.
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact / Backup (XP)

    I ran into the same error message when I tried that code and assumed I was missing a "piece" of code.

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compact / Backup (XP)

    Tools | Database utilities | Backup must be new in Access 2003, it is not available in Access 2002. Like compacting a database, you probably can't apply it to the currently open database in code, only interactively. The RunCommand acCmdBackup instruction can be used if you use Automation to open another database in code.

    (Note: acCmdBackup is available in Access 2002)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compact / Backup (XP)

    How would you like to backup the tables?

    DoCmd.TransferDatabase can be used to copy tables to another Access database.
    DoCmd.TransferSpreadsheet can be used to copy tables to an Excel workbook.
    DoCmd.TransferText can be used to copy tables to text files (fixed width or delimited.)

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact / Backup (XP)

    Hi,

    First, to clarify since, I have Office XP installed, and I went went back to see which version of Access comes with it and its listed as 2002.

    I tried docmd.transfer database and got the following error message..............."the type isn't and installed database type or doesn't support the operation you chose." I'm not sure what I'm missing.

    Thanks!

    Leesha

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compact / Backup (XP)

    Microsoft decided to make it complicated by using different version indications for Office as a whole and the individual applications. Office XP contains Word 2002, Excel 2002, Access 2002 etc. But many people call them Word XP, etc.

    The correct instruction is DoCmd.TransferDatabase, not DoCmd.Transfer Database, but it won't work by itself, you must specify what you want to transfer and where. Look up TransferDatabase in the online help for Visual Basic in Access.

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Compact / Backup (XP)

    My recommendation would be to split the database (if not already split) into back end (tables) and front end (queries, forms, reports, etc). Then when user exits the front end, run code to compact & backup the back end database file. Sample code:

    Public Sub CompactAndBackup(ByRef strPath As String, _
    ByRef SourceDB As String, _
    ByRef DestinationDB As String, _
    ByRef BackupDB As String)
    On Error GoTo Err_Handler

    Dim strMsg As String

    If Right$(strPath, 1) <> "" Then
    strPath = strPath & ""
    End If

    ' Ensure new DestinationDB (temp file) doesn't already exist:
    If Len(Dir$(strPath & DestinationDB)) > 0 Then
    Kill strPath & DestinationDB
    End If

    ' Copy uncompacted db to backup file (will overwrite existing file w/o warning):
    FileCopy strPath & SourceDB, strPath & SourceDB & ".BAK"

    ' Compact db into new db - User requires permission to open db exclusive (dbSecDBExclusive)
    DBEngine.CompactDatabase strPath & SourceDB, strPath & DestinationDB

    ' Copy new compacted db over uncompacted db:
    FileCopy strPath & DestinationDB, strPath & SourceDB

    ' Create backup folder if does not exist (subfolder of db's folder):
    If Len(Dir$(strPath & "Backup", vbDirectory)) = 0 Then
    MkDir strPath & "Backup"
    End If

    ' Copy compacted db to backup folder:
    FileCopy strPath & DestinationDB, strPath & "Backup" & BackupDB
    ' Delete temp file:
    Kill strPath & DestinationDB

    strMsg = "Back End database file compacted: " & vbCrLf & _
    SourceDB & vbCrLf & vbCrLf & _
    "Backup file: " & vbCrLf & BackupDB

    MsgBox strMsg, vbInformation, "COMPACT & BACKUP"

    Exit_Sub:
    Exit Sub
    Err_Handler:
    strMsg = "Error " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "COMPACT AND BACKUP ERROR"
    Resume Exit_Sub
    End Sub

    Public Sub TestCompactAndBackup()

    CompactAndBackup strPath:="C:Program FilesMicrosoft OfficeOfficeSamples", _
    SourceDB:="Northwind.mdb", _
    DestinationDB:="Temp.mdb", _
    BackupDB:="Backup.mdb"

    End Sub

    To keep things relatively simple, intrinsic VBA statements are used to copy & delete files, etc (as opposed to FSO or API methods). The CompactAndBackup procedure uses the DBEngine CompactDatabase method to compact/repair database into a temporary file, which is then copied over the original (uncompacted) file (the same thing pretty much happens when you compact a db "manually" via UI). FWIW this approach would be simpler than trying to "back up" tables that are in the current db (though that should not be too hard, using TransferDatabase method). As a general principle, the actual data (tables) should always be located in a separate back end file. This can be accomplished easily using the Database Splitter wizard (Tools, Utilities menu). To run compact/backup code, I usually use a hidden form that is opened when front end db opens, and closed before application exits, that calls the procedure from its Form Unload event. Backing up the front end file is usually not necessary; if it malfunctions, user just gets a new one as "replacement" (I use .MDE's for front end). NOTE: If testing code like this, always use COPIES of your actual database files, not originals!

    HTH

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact / Backup (XP)

    Hi Mark!

    As always ..............WOW! This is exactly what I was looking to accomplish. I do plan to spit the tables once the database is done or at least I'm sure there are no more table changes to be made (till the end users think of something else). I really appreciate the code and the time it took to spell it out for me!

    Have a great day counting beans!

    Leesha

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact / Backup (XP)

    Hi Mark

    Thanks for the code, works great as advertised

    Have you made any refinements since 05/21/04 that you want to share?

    Thanks, John

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Compact / Backup (XP)

    John,

    No really haven't made any changes since code was originally posted. One thing to note, at that time was using Access 2000 (aka A2K), am now using Access 2003. AFAIK there's been no changes to the DAO DBEngine.CompactDatabase method used in the sample code. However in Access 2002 (aka "Access XP"), a new method of the Access Application object was introduced, the Application CompactRepair method. Like the DAO method there are parameters where you specify a SourceFile and a DestinationFile. The only new thing I noticed is an optional LogFile argument - according to VBA Help, "True if a log file is created in the destination directory to record any corruption detected in the source file. A log file is only created if corruption is detected in the source file. If LogFile is False or omitted, no log file is created, even if corruption is detected in the source file." So if you think the log file may be useful, you could try using this method in place of the DAO CompactDatabase method. Note that the DAO method provides other options, such as the option to encrypt or decrypt the compacted db file, not provided by the newer method.

    The main issue with the Application CompactRepair method is same as with the older method, it cannot be used with the current database. As noted in Help: "The source file must not be the current database or be open by any other user, since calling this method will open the file exclusively." :-(

    HTH

Posting Permissions

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