Compact/Repair and Backup BE Database... Help!

[niuginikiwi]

I have come up with this function that check to see if record locking file
ldb exists for the backend database and if it doesn't , it copies the be db
to a new into another directory. Refer to code below...
But I want to do more than this, actually I would like to compact and repair
the database first and then do a backup copy. Can anyone add onto the
function below and show me how its done or even some tips and suggestions
would help.

Option Compare Database

Function DoBackup()
On Error GoTo Err_DoBackup


Dim strBackupFile As String
Dim strCurrentFile As String
Dim strDateStamp As String
Dim strCurrentLockFile As String



strCurrentFile = Application.CurrentProject.Path & "\DataFile.mdb"
DoCmd.Hourglass True
strDateStamp = Format(Date, "yyyy_mm_dd")
strBackupFile = Application.CurrentProject.Path & "\Backup\" &
strDateStamp & ".mdb"
strCurrentLockFile = Application.CurrentProject.Path & "\DataFile.ldb"

If Len(Dir(strCurrentLockFile)) > 0 Then
MsgBox "Cannot backup the database: it's in use", , "Backup "
Else
If Len(Dir(strBackupFile)) > 0 Then
Kill strBackupFile
End If
FileCopy strCurrentFile, strBackupFile
DoCmd.Beep
MsgBox "Database backup successful...", , "Backup Confirmation"

End If
' Copy the database
DoCmd.Hourglass False

Exit_DoBackup:
Exit Function


Err_DoBackup:
MsgBox Str(Err)
MsgBox Error$
Resume Exit_DoBackup

End Function

 

[BeWyched]

Hi

Replace your FileCopy line with:

Application.CompactRepair strCurrentFile, strBackupFile

This will Compact/Repair, copying the result to the new backup destination
with the new name. Note that the original BE will not be C/Repaired. If you
want this then, after the above, you could 'Kill' the original (or better
still rename it just-in-case) and reverse the process, e.g.:

Application.CompactRepair strCurrentFile, strBackupFile
Kill strCurrentFile ' or better to rename it
Application.CompactRepair strBackupFile, strCurrentFile

Cheers.

BW

 

[niuginikiwi]

Thanks BW,
That was what I might have just needed to get ahead. It works for me for now.
Another thing is that I got this strBackupFile pointing to F drive
(F:/Backups ) becoz I want to do the backup directly to F drive which is a
removable USB flash drive that I want to plug in and do the backup straight
onto it and take that away at the end of the day. But how do I check for the
existence of F drive, and if it doesn't exist, throw a friendly warning
saying that USB (F) drive is needed to proceed or something... this is
because sometimes someone may forget pluging in the USB removable drive and
just press the Backup command button to do a backup.
Any Ideas? Here is the line that is used at the moment to successfully
backup to the F drive.
strBackupFile = "F:\Backups\" & strDateStamp & ".mdb"

Thanks in advance

 

[BeWyched]

Hi

The easiest way is to attempt to access the drive and capture the error if
the connection cannot be made cos' the Memory Stick is missing:

On Error GoTo Err_No_F_Drive
Set fs = CreateObject("Scripting.FileSystemObject")
Set d = fs.GetDrive(fs.GetDriveName("F:"))
..... put your Compact/Repair coding here.....
Set d = Nothing
Exit Sub
Err_No_F_Drive:
MsgBox "You forgot the Memory Stick", 0, "Missing F: Drive"
Exit Sub

Note that you don't actually do anything with the 'd' object - its created
purely to prove that the F: drive is connected.

Please let me know how you get on.

BW

 

[niuginikiwi]

That again worked out to do the job as i was looking for....
THanks again BW

 

[Gntlhnds]

I know it's been a while since this was posted, but hopefully someone sees
this and can help. I copied this same code into my database and it works
great, unless I actually do have something open that is linked to the
backend, creating the ldb file, then instead of the error message in the code
I get a box that says "3356" with an "OK" button (that's all it says), and
then another box that says "Reserved Error" with an "OK" button. Shortly
after that the Access crashes. The code works great if I make sure
everything is closed first.

 

[Gntlhnds]

Actually, the code doesn't work as I had hoped. Since my backend resides on
a server and I run the code from the front end which is on my local machine,
it won't work. How can I incorporate an open file dialog to find the backend
and then compact/back up the file selected?