Compact / Backup (XP)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Leesha

Reply | Quote

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

Leesha

Reply | Quote

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)

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote