database back up

[Afrosheen]

I'm trying to backup my database through programming. The problem is I get an
error message saying: You can't compact the open database while running a
macro or VB program. I can run the File Maintenance form by its self and the
program will work fine. When I run it through the Main Menu form which
accesses the File Maintenance form then I get the message.

Here is the programming.

'---------------------------------------------------------------------------------------
' Procedure : cmdbackup_Click
' Author :
' Date : 9/8/2008
' Purpose :
'---------------------------------------------------------------------------------------
'
Private Sub cmdbackup_Click()
Dim strSQL
On Error GoTo cmdbackup_Click_Error

Select Case MsgBox("1} You'll be sent to the default folder. " _
& vbCrLf & "2} Select the last file with a date
attached. Right click on it and select delete. " _
& vbCrLf & "3} Answers any prompts. The file will
be deleted." _
& vbCrLf & "4} Click SAVE. After you've done the
back up the program will restart." _
& vbCrLf & " " _
& vbCrLf & "Note: The process will about 1-2
Minutes." _
, vbOKCancel Or vbInformation Or vbDefaultButton2,
Application.Name)

Case vbOK
strSQL = "UPDATE tblbackup SET tblbackup.bkdate = now()"
CurrentDb.Execute strSQL, dbFailOnError
BackupDatabase 'Backs up the database

Case vbCancel
Exit Sub
End Select


On Error GoTo 0
Exit Sub

cmdbackup_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdbackup_Click of VBA Document Form_Form2"
End Sub

Because of who I'm writing this for is not computer savy, I'd like it to
delete the last backup and create a new back up. I don't know if this can be
done. If not then we'll have to deal with it.

Thanks for reading my post

 

[dch3]

I'd do this via VBScript that uses the File System Object to do everything.

http://msdn.microsoft.com/en-us/library/z9ty6h50.aspx

 

[Afrosheen]

Thanks for the reply. It looks like it creates a txt file. Is that correct?

What I want to do is have the access database back its self up so if someone
messes it up I can go back and get the copy.

 

[dch3]

The example that I sent probably does. I sent the link to provide you with
more information on the FSO and what it can do. The FSO allows file system
operations - such as creating a copy of a file to be executed via code. By
doing the backup via VBS, you won't have to worry about the file being open -
unless it is anyways. In short, to my knowledge you can't backup a database
while the database is open. Also, you can entirely create a VBS script that
backsup the database and then launches Access. I use something similar to
automatically distribute new front ends. In my scenario, my users are trained
to start the application using the script (which looks like a Access
shortcut). The script copies the current front end to their PC and then
launches the app.

 

[Dennis]

You cannot maipulate a database while it's running something. You need to
have the database CLOSED to do what you want. In that event, you need to
execute your backup routine from a separate database mini-application (or
using VBS as has been discussed).

 

[Afrosheen]

Again, Thanks for the help. It looks like I'll have to do the back up my self.

Another thought. Can the backup using this code be done when they exit the
program? Just wondering.

 

[dch3]

NO. The database MUST be completely closed and shut down in order to back it
up. As I mentioned before, I would use a VBS script that creates the backup
and then launches the application. That way, the backup is being done as the
users are starting their work and they don't know any difference.

 

[Alex Dybenko]

Hi,
if you have backend and frontend - you can use this solution to backup a
backend:
http://www.pointltd.com/Downloads/Details.asp?dlID=49

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com