Hi,
I got these three code routines for backing up an Access database file from two different forums. I installed them as is, on a test database, creating command buttons for each. The test database is not password protected. I intend (if I can get one of these working) to install it on simple, single file, password protected databases.
The first two came from Access World Forums at: http://www.access-programmers.co.uk/forums/showthread.php?t=226520
I got a compile error "Invalid use of property." on the button sub. I found this code rather confusing.
Then there was a simple On Click sub:
On this one I got a "Permission denied." error on the CopyFile method. As I said before, my database is not password protected. This code seemed overly simplistic, but apparently worked fine for aussie and sk.shafiqul, just not for me.
I found this third bit of code on another forum: http://mybusinessdatabase.com/vba-code-to-backup-an-access-database/
On this one I got another "Permission denied." error on the CopyFile method. Again, my database is not password protected. This code had appeared to me to be the most promising of the three.
Can anyone tell me which of these is the most likely to work without totally rewriting it and why these errors might be happening? Any help would be greatly appreciated.
I got these three code routines for backing up an Access database file from two different forums. I installed them as is, on a test database, creating command buttons for each. The test database is not password protected. I intend (if I can get one of these working) to install it on simple, single file, password protected databases.
The first two came from Access World Forums at: http://www.access-programmers.co.uk/forums/showthread.php?t=226520
Code:
Function BACKUPS()
On Error GoTo Err_BACKUPS
Dim fso As Object
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String
'Dim db As New Access.Application
Dim buf As String
Dim strBu As String
sSourceFile = CurrentProject.Name
sSourcePath = CurrentProject.Path & "\"
sBackupFile = CurrentProject.Name
sBackupPath = CurrentProject.Path & "\Backups\"
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
Set fso = Nothing
Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sBackupFile, vbInformation, "Backup Completed"
Exit_BACKUPS:
Exit Function
Err_BACKUPS:
MsgBox Err.Number & Err.Description
Resume Exit_BACKUPS
End Function
Then there was a simple On Click sub:
Code:
Private Sub Backup_Click()
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile CurrentProject.FullName, "C:\", True
Set fs = Nothing
MsgBox "Database has been backed up successfully"
End Sub
Private Sub Command32_Click()
End Sub
I found this third bit of code on another forum: http://mybusinessdatabase.com/vba-code-to-backup-an-access-database/
Code:
Public Function db_Backup()
On Error GoTo db_Backup_Err
Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String
sourceFile = CurrentProject.FullName
path = CurrentProject.path
name = CurrentProject.name
destinationFile = path & "\db backups - please do not remove\" & _
Left(name, Len(name) - 6) & "_backup" & "_" & _
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".accdb"
'this removes a file created on the same day
If Dir(destinationFile) <> "" Then
Kill destinationFile
End If
'this creates a backup into destination path
If Dir(destinationFile) = "" Then
Set aFSO = CreateObject("Scripting.FileSystemObject")
aFSO.CopyFile sourceFile, destinationFile, True
MsgBox "A database backup has been stored under " & destinationFile
End If
db_Backup_Exit:
Exit Function
db_Backup_Err:
MsgBox ("Error in db_Backup function" & vbCrLf & _
"Error: #" & Err.Number & vbCrLf & _
"Description: " & Err.Description)
Resume db_Backup_Exit
End Function
Can anyone tell me which of these is the most likely to work without totally rewriting it and why these errors might be happening? Any help would be greatly appreciated.
Last edited: