Backup Database-Consolidating VBA

M

Mac_Wood

Registered User.
Local time
Today, 01:45
Joined
Dec 10, 2005
Messages
65
I am very grateful to ghudson and jon jamaco for the two examples of VBA that I have used in my 2002-2003 Format Database.
I needed like many others in this forum to backup my database but I was a little unsure how to use ghudson's code correctly. Jon jamaco's code worked a treat but I did not want to save my backup copy in the root directory.
I have attempted to consolidate the two sets of code to save a copy in another folder but so far my new sub has been unsuccessful in completing the task.
I have attached my edited code below:
Code: 
Private Sub Command325_Click()
Dim bckup As FileSystemObject
On Error GoTo Unsuccessful


Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "I:\EurobipDBs\"
sSourceFile = "euroBIPFromNov05_2008-04-22_(1).mdb"
sBackupPath = "I:\EurobipDBs\Backups"
sBackupFile = "EurobipBackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

Set bckup = New FileSystemObject
    bckup.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
Set bckup = Nothing

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sSourcePath & sSourceFile, sBackupPath & sBackupFile & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sBackupFile, vbInformation, "Backup Completed"

End
Unsuccessful:
MsgBox "Backup unsuccessful. Check:" & Chr(13) & Chr(13) & "- The disk you are saving to is not full" & Chr(13) & Chr(13) & "AND" & Chr(13) & Chr(13) & "- That a 'backups' folder has been created in the directory of the active database", vbExclamation, "Backup Unsuccessful"

The two unedited samples are attached below:
Code: 
 Public Function BackupCopy()
'This function will allow you to copy a db that is open,

'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

Dim fso As FileSystemObject

Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "F:\EurobipDatabaseBackups\"
sSourceFile = "euroBIPFromNov05_2008-04-22_(1).mdb"
sBackupPath = "F:\EurobipDatabaseBackups\"
sBackupFile = "EurobipBackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

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"

End Function
Code: 
 Private Sub Command325_Click()
Dim bckup As FileSystemObject
On Error GoTo Unsuccessful

Dim strBckupName As String
    strBckupName = "BackupDB - " & Format(Date, "mm.dd.yyyy") & " - " & Format(Time, "hhmmss") & ".mdb"

Set bckup = New FileSystemObject
    bckup.CopyFile Application.CurrentProject.FullName, Application.CurrentProject.Path & "\Backups\" & strBckupName, True
Set bckup = Nothing

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & Application.CurrentProject.Path & "\Backups" & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & strBckupName, vbInformation, "Backup Completed"

End
Unsuccessful:
MsgBox "Backup unsuccessful. Check:" & Chr(13) & Chr(13) & "- The disk you are saving to is not full" & Chr(13) & Chr(13) & "AND" & Chr(13) & Chr(13) & "- That a 'backups' folder has been created in the directory of the active database", vbExclamation, "Backup Unsuccessful"
End Sub

Any assistance gratefully received.
 
Mac_Wood said:
I am very grateful to ghudson and jon jamaco for the two examples of VBA that I have used in my 2002-2003 Format Database.
I needed like many others in this forum to backup my database but I was a little unsure how to use ghudson's code correctly. Jon jamaco's code worked a treat but I did not want to save my backup copy in the root directory.
I have attempted to consolidate the two sets of code to save a copy in another folder but so far my new sub has been unsuccessful in completing the task.
I have attached my edited code below:
Code: 
Private Sub Command325_Click()
Dim bckup As FileSystemObject
On Error GoTo Unsuccessful


Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "I:\EurobipDBs\"
sSourceFile = "euroBIPFromNov05_2008-04-22_(1).mdb"
sBackupPath = "I:\EurobipDBs\Backups"
sBackupFile = "EurobipBackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

Set bckup = New FileSystemObject
    bckup.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
Set bckup = Nothing

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sSourcePath & sSourceFile, sBackupPath & sBackupFile & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sBackupFile, vbInformation, "Backup Completed"

End
Unsuccessful:
MsgBox "Backup unsuccessful. Check:" & Chr(13) & Chr(13) & "- The disk you are saving to is not full" & Chr(13) & Chr(13) & "AND" & Chr(13) & Chr(13) & "- That a 'backups' folder has been created in the directory of the active database", vbExclamation, "Backup Unsuccessful"

The two unedited samples are attached below:
Code: 
 Public Function BackupCopy()
'This function will allow you to copy a db that is open,

'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

Dim fso As FileSystemObject

Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "F:\EurobipDatabaseBackups\"
sSourceFile = "euroBIPFromNov05_2008-04-22_(1).mdb"
sBackupPath = "F:\EurobipDatabaseBackups\"
sBackupFile = "EurobipBackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

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"

End Function
Code: 
 Private Sub Command325_Click()
Dim bckup As FileSystemObject
On Error GoTo Unsuccessful

Dim strBckupName As String
    strBckupName = "BackupDB - " & Format(Date, "mm.dd.yyyy") & " - " & Format(Time, "hhmmss") & ".mdb"

Set bckup = New FileSystemObject
    bckup.CopyFile Application.CurrentProject.FullName, Application.CurrentProject.Path & "\Backups\" & strBckupName, True
Set bckup = Nothing

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & Application.CurrentProject.Path & "\Backups" & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & strBckupName, vbInformation, "Backup Completed"

End
Unsuccessful:
MsgBox "Backup unsuccessful. Check:" & Chr(13) & Chr(13) & "- The disk you are saving to is not full" & Chr(13) & Chr(13) & "AND" & Chr(13) & Chr(13) & "- That a 'backups' folder has been created in the directory of the active database", vbExclamation, "Backup Unsuccessful"
End Sub

Any assistance gratefully received.
Click to expand...

What error message, if any, are you receiving?

I've been using a database backup by Helen Feddema from 2003. I see there is a link to one of her articles here: http://office-watch.com/access/archtemplate.asp?8-02
 
Simple Software Solutions

Hi All

I have a backup routine that I use in VB. It respondes to on demand backups and also on application unload event. Similar naming conventions using the date to create a file name. The only difference is that my routine actually takes the mdb and drops it into a zip file. You can specifiy where the file is saved by default and also react to overwrite prompts if the file has already been backed up that date.

If this is something that appeals to you let me know and I will post the sample mdb with instructions to the forum.

What I have not told it to do but I am woring on it is to prompt the user if they want to compact the mdb before it is backed up.

CodeMaster::cool:
 
Simple software Solutions

I would be very interested in seeing your mdb backup DCrake. Thanks very much.
 
Getting error after splitting database

Sorry to jump the post but, I am using the same backup routine and before I split my database everything worked fine. Now that I have split I get an error at "Dim fso As FileSystemObject" declaration. 'User-defined Type not defined' has anyone else run into this?
 
FLCoderMike said:
Sorry to jump the post but, I am using the same backup routine and before I split my database everything worked fine. Now that I have split I get an error at "Dim fso As FileSystemObject" declaration. 'User-defined Type not defined' has anyone else run into this?
Click to expand...

I think you are missing a reference.
You need to reference the Microsoft Scripting Runtime
 
Simple Software Solutions

Please find attached demo. Remember to read the How To document before runnig the demo. It is important as you have to register a dll first. If you employ this functionality in your mdb remember to register the dll in your references.

CodeMaster::cool:
 

Attachments

Run-time error 13

Hi DCrake,

I have tried your zip up db but I am having problems with the Tmp() function. I have registered the dll and referenced it in my db and added the function to a module but when I type the question ?Tmp() in the Immediate window I get a Runtime error 13 Type mismatch, and the debugger highlights
Code: 
Set P = DB.CreateProperty("DataPath", DB_TEXT, "F:\Documents")

Do you have any idea why this is?
 
Simple Software Solutions

I received this error in one of my databases and I could not resolve it, however if you create a blank database and create the porperties in there it works fine. Then you can import your original database into the new one and use that.

David
 
You must log in or register to reply here.

Similar threads

O
Backup code getting "Permission denied" error
2
Replies
21
Views
4,591
vbaInet
V
K
Vba code for backup issue
Replies
10
Views
4,759
sk.shafiqul
S
T
Retrieving Database Backups
Replies
0
Views
626
Terry Lawson
T
GavZ
Backups
Replies
3
Views
1,104
GavZ
GavZ
A
Backup without Zipping
Replies
5
Views
1,098
Sheila.deJesus
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom