Backup Current DB ? 9

[SKing44]

I hope somebody can answer this.....

I am trying to create a function which will back up my currently open DB upon request, but not to the same file, for example My DB is H:\Files\DB\Sample.mdb, and I want to save it to C:\Files\Backup\sample.mdb (As the same file name).

Can this be done in Access 97 ?

Thanks

 

[MichaelRed]

Well, ? Sort of, at least for a little while. But it is NOT a good soloution.

Write the VBA procedure which writes a Command (A.K.A. / NEE BATCH) file and execute it. Under DOS (and I THINK) the command processor, you can copy an 'Open' file. Under any current ver of WIN, You CANNNOT. And - there is NO guarntee that the copied file (esp a database file) will copy w/o errors. Typically, if a record is written to a table during the process of copying THAT table, you are LIKELY to get an error.

An alternative -which IMHO - is both better and safer - is to create a NEW mdb file and populate it by copying objects from the curr db to the 'backup' - on an object-by-object basis. In general, once you have the 'Original' backup, you can save some (considerable) effort by checking the date modified field and only copy 'new' and updated items.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jimmythegeek]

Use the FileSystemObject. Copy the sub routine below into a module. Then make sure you have proper reference. While in the module design view go to Tools | References and select "Microsoft Scripting Runtime" from the list

You can now call this sub from a button click, or some other procedure. It will copy the database as it currently exists. You can change the source and destination to whatever you want.

-----------------------------

Public Sub BackUpDB()
Dim fso As FileSystemObject
Dim src As String, dest As String

src = "H:/Files/DB/Sample.mdb"
dest = "C:/Files/Backup/sample.mdb"

Set fso = New FileSystemObject

fso.CopyFile src, dest, True

Set fso = Nothing
End Sub
Jim Lunde
compugeeks@hotmail.com
We all agree that your theory is crazy, but is it crazy enough?

 

[MichaelRed]

Hmmmmmmmmmmm,

Hats off to Jimmy. I haven't tried this on a multiuser db "In Use", however it DOES copy the db -even though it is open, whichI thought was a windows NO-No. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jimmythegeek]

I would agree that it is not the ideal thing to do (copy an open database), but sometimes you gotta do what ya gotta do . I use it for copying stuff all the time. Actually the FileSystemObject is pretty powerful, it does alot more than copy files. Jim Lunde
compugeeks@hotmail.com
We all agree that your theory is crazy, but is it crazy enough?

 

[snoopy75]

Nice... I didn't know about FileSystemObject. I'll have to play with it a little.

--Ryan

 

[Quehay]

Here's another star Jim!

I use a similar routine, using the File System Object to make a local PC copy of the linked Front End from a network master copy.

To back up all the back end files for various network db's (they're kept in a common folder) I use a VB executable that employs the File System Object; then the Task Scheduler is used to do systematic backups three times a day (using Format function with Now() allows creation of a backup copy of the entire folder with a timestamp tag on the folder name--thus you can have an archive of backups in the same place).

Access never minds if you copy an open file.

Another sleeper is the Windows Scripting Host--you can do some cool stuff with it, like creating user shortcuts.

 

[Rohdem]

"sometimes you gotta do what ya gotta do"

Man, do I HEAR that!!!!!

Nice code, Jimmy!! Mike Rohde
"I don't have a god complex, god has a me complex!"

 

[LonnieJohnson]

Jim Jim Jim...

Just when I thought I knew something, someone else comes along knowing more. Thank God for that.

Nice move. This is the move of the month for me. I have a copying routine that this is going to star in.

You da man, yep you da man!!!

Thanks agian.
prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[Quehay]

Here's the code (it was at work) for two distinct VB executables that I use to back up multiple back ends at one time (they're in a common folder). I use the Task Scheduler to run backup 3X a day and then run the "purge" routine once a week to get rid of unnecessary backup archives:

Jim's method is ideal if you have one db. You can run the backup at the close event a form to make it automatic.

[tt]

'BACKUP ROUTINE

Private Const c_TargetFolder As String = "\\Folder\MyBackups\"

Private Const c_SourceFolder As String = "\\Folder\MyTables"
Public Sub Main()
On Error GoTo Err_Backup

Dim strBackUpFolder As String

Dim FSO As New FileSystemObject

Dim strDateTag as String

strDateTag = Format(Now, "mmm_dd_hh-mm&quot

strBackUpFolder = c_TargetFolder & "Backup_" & strDateTag

FSO.CopyFolder c_SourceFolder, strBackUpFolder, True

MsgBox "Backup Successful for " & strDateTag, vbExclamation, "BACKUP COMPLETED"

Exit_Err_Backup:
Exit Sub

Err_Backup:
MsgBox "Backup failed due to: " & vbCrLf _
& Err.Number & ": " & Err.Description, vbCritical, "BACKUP FAILED"
Resume Exit_Err_Backup
End Sub

'BACKUP FOLDER PURGE--anything over 5 days old

Option Explicit

Private Const c_ExistingBackups As String = "\\folder\MyBackups\"
Private Const c_NumDaysPrevious As Integer = 5

Public Sub Main()
On Error GoTo Err_Backup

Dim FSO As New FileSystemObject
Dim BkFldr As Folder
Dim Fldr As Folder
Dim dtmRefDate As Date

dtmRefDate = ((Date) - c_NumDaysPrevious)

Set BkFldr = FSO.GetFolder(c_ExistingBackups)

For Each Fldr In BkFldr.SubFolders

If Fldr.DateCreated < dtmRefDate Then

Fldr.Delete

End If

Next


MsgBox &quot;Backup Folder Purge Successful for &quot; & Format(Now(), &quot;mmm dd hh:mm&quot, vbExclamation, &quot;PURGE COMPLETED&quot;

Exit_Err_Backup:
Exit Sub

Err_Backup:
MsgBox &quot;Purge failed due to: &quot; & vbCrLf _
& Err.Number & &quot;: &quot; & Err.Description, vbCritical, &quot;PURGE FAILED&quot;
Resume Exit_Err_Backup
End Sub
[/tt]

 

[TomIII]

Nic piece, Jim...

I've been looking for this solution. Here's a belated Star.

 

[Guest_imported]

Thanks guys, I've been looking for this code.

 

[bocker]

Question??

If you wanted to use winzip and copy to a disk on drive A:, is there code that could be used to do this????

 

[jerseyboy]

Quehay,
I am like a toddler in the candy shop! Thanks! I just have one question, what should be done with the .ldb file that is copied along with the folder? I could KILL it, but would that change something?
Thanks
JerseyBoy