Access Database - Visual Basic Save Button (1 Viewer)

[Lord Max]

Hi,

I need some help please.

I'm trying to code a 'Save Button' which I can put on a 'Backup Form' to allow the users of my database to save a backup of the database. If possible, I would like to have the code auto fill in the 'Save As' with the day's date, like 28-08-10.

Anyone know the code of how I could do this?

Thanks in advance!!

 

[jfgambit]

Try the following:

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

 

[Lord Max]

Try the following:

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

That's great thank you!

It works, up to this line, where something is apparently wrong:

fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup

Please advise! Thank you very much!

PS: Could you sort of talk me through what this code is doing at each part? I'm quite new to VB and only know the basics.

 

[Lord Max]

That's great thank you!

It works, up to this line, where something is apparently wrong:

fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup

Please advise! Thank you very much!

PS: Could you sort of talk me through what this code is doing at each part? I'm quite new to VB and only know the basics.

Apologies. That was just me having a blonde moment; even though I'm not blonde. That works fantastically well and is exactly what I wanted. Thank you so very much!

Although, if possible, and not too much trouble, would you be able to give a brief description of what the code does at it's steps?

Thank you very much again, such a quick and perfect answer!

 

[jfgambit]

Sure...

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

'Get current path of the database currently open
dbPath = Application.CurrentProject.Path

'Current database name
OldDbName = "OldDatabaseName.mdb"

'Creates the new name of the backup database by combining the names and current date
NewDbName = "BackUp" & "_" & Format(Date, "yyddmm") & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "yyddmm") & ".mdb"


Prompts user to ensure they want to make a backup copy
If MsgBox("Do you want to make a compacted back-up copy of this database named " & vbCrLf & "'" & NewDbName & "'", vbYesNo, "Continue") = vbYes Then

If user confirms, then copy the file through scripting to the path the current database resides and call the new name as defined above
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
DBEngine.CompactDatabase dbPath & "\" & DbBackup, dbPath & "\" & NewDbName

Drops the object after the backup is created
Set fs = Nothing
Else

'If user clicks 'NO' to the backup...do nothing
End If

Glad we can help.

 

[Lord Max]

Sure...

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

'Get current path of the database currently open
dbPath = Application.CurrentProject.Path

'Current database name
OldDbName = "OldDatabaseName.mdb"

'Creates the new name of the backup database by combining the names and current date
NewDbName = "BackUp" & "_" & Format(Date, "yyddmm") & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "yyddmm") & ".mdb"


Prompts user to ensure they want to make a backup copy
If MsgBox("Do you want to make a compacted back-up copy of this database named " & vbCrLf & "'" & NewDbName & "'", vbYesNo, "Continue") = vbYes Then

If user confirms, then copy the file through scripting to the path the current database resides and call the new name as defined above
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
DBEngine.CompactDatabase dbPath & "\" & DbBackup, dbPath & "\" & NewDbName

Drops the object after the backup is created
Set fs = Nothing
Else

'If user clicks 'NO' to the backup...do nothing
End If

Glad we can help.

Thanks for that, it really clarifies it all in my head.

One thing - when I back it up, it created two files, one being 'OldDatabaseName_yymmdd' and the other being 'BackUp_yymmdd'. Why does it do this and is there a way to get around it from making two backups, and just the one?


Thanks again

 

[jfgambit]

Sorry...add the kill line below...

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
[I]Kill dbPath & "\" & NewDbName[/I]
Set fs = Nothing
Else

End If

 

[Lord Max]

Sorry...add the kill line below...

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
[I]Kill dbPath & "\" & NewDbName[/I]
Set fs = Nothing
Else

End If

Alright, thanks. That's perfect. Mind if I ask why and what this 'Kill' line does as I don't see why it used the dbPath.

Thanks again.