Making a back up of a database

[branston]

Good morning,

I have a databases. One of the forms in that database has a button. When i press this button i want it to make a copy of every single table, query, form and report into a new database in a specific location with the filename BackUp and then the date (eg "BackUp" & now() )

Eventually I will have a few 'back-end" databases with all the queries/forms/reports in a "front-end", so i will need the code to be able to copy databases other than the current one. Is that possible?

Ive found a couple of posts on the web about this sort of thing, but im having real trouble with it so if someone could spell it out (Im self taught so missing vital "simple" bits!) that would be great.

Thanks in advance!

 

[Guus2005]

when there are no other users you could easilly create a backup using the compactdatabase function. Using this statement you create a compacted backup of the database of your choice. There is no need to export each object separately.

HTH

 

[branston]

Oh right... Just done a quick search on compact database, and it sounds like it would be wonderful and easy and work perfectly, but... the database is going to be used by lots of users.
I dont spose there is something just as easy for multiple users?

 

[branston]

Hey. I just found some code that seems to do the trick... It also compacts the database which is quite handy:

Dim dbPath As String
Dim OldDbName As String
Dim NewDbName As String
Dim DbBackup As String
Dim Response As Integer
Dim fs As Object

dbPath = Application.CurrentProject.Path
OldDbName = "OldDatabaseName.mdb"
NewDbName = "BackUp" & "_" & Format(Date, "yyddmm") & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "yyddmm") & ".mdb"

If MsgBox("Do you want to make a compacted back-up copy of this database named " & vbCrLf & "'" & NewDbName & "'", vbYesNo, "Continue") = vbYes Then
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
DBEngine.CompactDatabase dbPath & "\" & DbBackup, dbPath & "\" & NewDbName
Set fs = Nothing
Else

End If


Hope this helps others out there!
And thanks for your message Guus2005, that may well come in handy in the future!!

 

[jdraw]

Here is a link that will export all of the objects in an mdb database as text. This will create a "backup copy of all objects". There is an option to import and recreate all objects. This can be very useful in resolving corrupt database issues.

http://www.peoplelinks.co.uk/msaccess/downloads/EatBloatV4-A2003.zip

 

[branston]

Thanks JDraw, Im sure that will come in handy!

 

[MrNooby]

Nice code, just 1 question:

What if I use password for my database, where can I add this in code, because now when I try to backup I get error 3031 - wrong password.

 

[shieriel]

Hey. I just found some code that seems to do the trick... It also compacts the database which is quite handy:

Dim dbPath As String
Dim OldDbName As String
Dim NewDbName As String
Dim DbBackup As String
Dim Response As Integer
Dim fs As Object

dbPath = Application.CurrentProject.Path
OldDbName = "OldDatabaseName.mdb"
NewDbName = "BackUp" & "_" & Format(Date, "yyddmm") & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "yyddmm") & ".mdb"

If MsgBox("Do you want to make a compacted back-up copy of this database named " & vbCrLf & "'" & NewDbName & "'", vbYesNo, "Continue") = vbYes Then
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
DBEngine.CompactDatabase dbPath & "\" & DbBackup, dbPath & "\" & NewDbName
Set fs = Nothing
Else

End If


Hope this helps others out there!
And thanks for your message Guus2005, that may well come in handy in the future!!

Hi! I've been looking for this a long time here and i am very thankful that i found it! But i want to make some changes on the code i just dont know how. I want the backup to be automatic and not asking for a confirmation from the user but instead a pop-up message saying, "Saving and creating Backup".
How can i do that?

 

[xarxas]

Hi! I've been looking for this a long time here and i am very thankful that i found it! But i want to make some changes on the code i just dont know how. I want the backup to be automatic and not asking for a confirmation from the user but instead a pop-up message saying, "Saving and creating Backup".
How can i do that?

just remove the popup code:

Dim dbPath As String
Dim OldDbName As String
Dim NewDbName As String
Dim DbBackup As String
Dim Response As Integer
Dim fs As Object

dbPath = Application.CurrentProject.path
OldDbName = "OldDatabaseName.mdb"
NewDbName = "BackUp" & "_" & Format(Date, "yyddmm") & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "yyddmm") & ".mdb"

'========= automatic without msgbox =============
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
DBEngine.CompactDatabase dbPath & "\" & DbBackup, dbPath & "\" & NewDbName
Set fs = Nothing

enjoy ^_^

 

[shieriel]

just remove the popup code:

enjoy ^_^

Thanks so much! The code is working fine. I just noticed that it is creating Backup as well as a copy of the file. I tried to edit the code to remove the "copy" but still no luck. How should i edit the code that only backup is creating.

Many thanks in advance..

 

[xarxas]

Thanks so much! The code is working fine. I just noticed that it is creating Backup as well as a copy of the file. I tried to edit the code to remove the "copy" but still no luck. How should i edit the code that only backup is creating.

Many thanks in advance..

you need "the copy" function there in order to make the backup. but you can delete the file afterward.