Automatically Backup an Access Database

[ScardyBob]

Hello All!

I wanted to implement an automated backup routine that would
essentially do what 'File' - 'Back Up Database' does in Access. I made
a crude routine (located below) that accomplishes what I want but it
creates and executes a VBScript file. I've been reading where this can
be disabled or blocked on some systems which would cause my backup to
fail. Is there a better way to do this (i.e. not using VBScript), or
are my concerns about VBScript being blocked unfounded?

Thanks,
Mike

Private Declare Function ShellExecute Lib "shell32.dll" Alias
"ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As
String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Public Sub BackupDatabase()

Dim AccessFile As String
Dim BackUpDir As String
Dim SourceFile As String
Dim DestinationFile As String
Dim VBScriptFile As String

BackUpDir = CurrentProject.Path & "\ASCBackups\"
AccessFile = CurrentProject.Name
SourceFile = CurrentProject.Path & "\" & AccessFile
DestinationFile = CurrentProject.Path & "\ASCBackups\" _
& Left(CurrentProject.Name, Len(CurrentProject.Name) - 4) _
& "_BACKUP(" & FORMAT(Now, "mm-dd-yyyy_hh-mm") & ").mdb"

VBScriptFile = BackUpDir & "ASCBackupExecute.vbs"

Open VBScriptFile For Output As #1
Print #1, "Dim ac" & vbCrLf
Print #1, "Dim SourceFile" & vbCrLf
Print #1, "Dim DestinationFile" & vbCrLf & vbCrLf
Print #1, "Set ac = GetObject(" & Chr(34) & SourceFile & Chr(34) & ")"
& vbCrLf
Print #1, "ac.Quit" & vbCrLf
Print #1, "SourceFile = " & Chr(34) & SourceFile & Chr(34) & vbCrLf
Print #1, "DestinationFile = " & Chr(34) & DestinationFile & Chr(34) &
vbCrLf & vbCrLf
Print #1, "Set Fso = CreateObject(" & Chr(34) &
"Scripting.FileSystemObject" & Chr(34) & ")"
Print #1, "Fso.CopyFile SourceFile, DestinationFile, True" & vbCrLf &
vbCrLf
Print #1, "Set ac = CreateObject(" & Chr(34) & "Access.Application" &
Chr(34) & ")" & vbCrLf
Print #1, "ac.OpenCurrentDatabase " & Chr(34) & SourceFile & Chr(34)
Print #1, "ac.Run " & Chr(34) & "BackupDatabaseCleanup" & Chr(34) & ",
" & Chr(34) & BackUpDir & "ASCBackupExecute.vbs" & Chr(34)
Print #1, "Set ac = Nothing" & vbCrLf
Print #1, "Set Fso = Nothing" & vbCrLf
Print #1, "SourceFile = " & Chr(34) & Chr(34) & vbCrLf
Print #1, "DestinationFile = " & Chr(34) & Chr(34) & vbCrLf
Close #1

OpenFileInDefaultApp (BackUpDir & "ASCBackupExecute.vbs")

GoTo Exit_Section

Exit_Section:
AccessFile = ""
BackUpDir = ""
SourceFile = ""
DestinationFile = ""
VBScriptFile = ""
Exit Sub

End Sub

Sub OpenFileInDefaultApp(FullName As String)
ShellExecute 0, vbNullString, FullName, 0&, 0&, 1
End Sub

Public Sub BackupDatabaseCleanup(ByVal VBFileLoc As String)
Kill VBFileLoc
End Sub

 

[Guest]

Do you mean VBA?

VBA is enabled if a user chooses to allow it. And of course it can be
disabled once a db is open by merely changing the security settings in the
Access application.

Of course, if you've turned the db into a standalone application perhaps
this situation is a bit different.

 

[ScardyBob]

Dave,

I saved the text in Sub BackupDatabase as a ".vbs" file. As I
understand this is a type of windows scripting that can use VBA. I may
be wrong though. I used this method since FileCopy (which does what I
want) in VBA requires the file to be copied to be completely closed.
Therefore, I couldn't copy the current database file with FileCopy and
needed something outside of the VBA in my database to do the actual
copying. Clearly, Access already has this functionality with its 'Back
Up Database', but I haven't found a way to run that directly. Any
suggestions?

Thanks,
Mike

 

[Douglas J. Steele]

I'd recommend very strongly against what you're doing.

While VBS can copy a file that's in use, that doesn't mean it's a good idea.
Copying the active database can mean that the resultant copy is in an
inconsistent state, so that it would be useless to you.

Take a look at the TSI SOON (Shut One, Open New) database add-in that MichKa
has for free at http://www.trigeminal.com/utility.asp?ItemID=8#8

 

[Guest]

I am assuming that you want to copy the Back-end file. you can try the
following VBScript coding.


Option Explicit
On Error Resume Next

'****************************************************************************
'* This script Copies a database file *
'****************************************************************************
'----------------------------------------------------------------------------
' Instantiate the script objects
'----------------------------------------------------------------------------
Dim objNet : Set objNet = CreateObject("WScript.Network")
Dim objFS : Set objFS = Wscript.CreateObject("Scripting.FileSystemObject")
Dim WSHShell : Set WSHShell = Wscript.CreateObject("Wscript.Shell")
Dim objFolder, BtnCode

'----------------------------------------------------------------------------
' Copy file(s)
'----------------------------------------------------------------------------
btnCode = wshShell.Popup("Please wait...Updating your files...", 1,
"Copying...", 64)
objFS.Copyfile "X:\FolderName\FileName.mdb",
"C:\BackupFolderName\BackupFilename.mdb"
btnCode = wshShell.Popup("Your file(s) have been updated. This window
will close in 3 seconds...", 3, "ALL DONE!", 64)

'----------------------------------------------------------------------------
' Release objects from memory and terminate execution
'----------------------------------------------------------------------------
Set objNet = nothing
Set objFS = Nothing
Set WSHShell = nothing
Set objFolder = nothing
Set objFolder = nothing
WScript.Quit

Do not type the following notes as part of the script:

X is the letter that identifies your server drive
repeat the line objFs.Copyfile... for each file that you want to copy
Make sure to type the extension .mdb on each file name
Create teh fiel in NOTEPAD and save it as "all files" with the extension .VBS
You can make the script a part of the network scheduled tasks, you can run
any time you want as a desktop shortcut, it can run evrytime the user logs
in, etc.
It will copy the database file even if it is open or shared.

 

[Douglas J. Steele]

No need for VBS to copy the back-end.

Simply make sure there are no connections to it (i.e.: check that the LDB
file doesn't exist), and you can use the VBA FileCopy command. What I
typically do, though, is rename the back-end (or move it to where you want
it), then compact that copy using the DBEngine's CompactDatabase method,
naming the compacted database to whatever the back-end you renamed was
named.

As I stated elsewhere in this thread, the fact that FSO lets you copy files
that are in use doesn't mean it's a good idea!

 

[ScardyBob]

Ricoy,

That worked great! Thanks! I was hoping to avoid using VBScript and
instead use VBA directly or the Back Up functionality in Access, but
its not looking likely.

Doug,

I don't want to copy a database in use, but make a backup copy at
either a scheduled time (say at 3:00 am when (hopefully) no one is
using it) or by pushing a button, which closes, makes the copy, and
then reopens the database. I included:
Dim ac
Set ac = GetObject("DatabaseFileName")
ac.Quit
in my first VBScript attempt which seemed to close the database
correctly (no MSACCESS.EXE in task manager). It seems that this is the
way the Access 'Back Up Database' works so I figured I could either use
it directly through VBA (so far unsuccessful) or simulate this behavior
via some outside program. Is there any way to use Access's Back Up
program so I can avoid this mess?

Thanks!
Mike

 

[Douglas J. Steele]

The fact that MSACCESS.EXE isn't in Task Manager doesn't mean that no one's
using the MDB.

MSACCESS.EXE runs on each client, not the server.

Realistically, the only way to know whether a particular MDB is in use is to
check whether its LDB exists.

 

[ScardyBob]

Doug,

Unfourtunately, its all one database, no FE/BE. I did see a few threads
that addressed copying the back-end and I may go that way if this gets
to complex. I was hoping to figure out how to do it on the current
database without causing problems (bycompletely closing it before I
made the back up). I was hoping I could confine it simply to Access
VBA, but its not looking good. Thanks for the help!

Thanks,
Mike

 

[Douglas J. Steele]

You should definitely split the application into a front-end (containing the
queries, forms, reports, macros and modules) and a back-end (containing the
tables and relationships). I'd suggest doing this even if you've only got a
single user, and both files are on the client.

I'll repeat my advice not to use VBS to copy an open database.

 

[aaron.kempf]

DIPSHITS!!

Keep your data in SQL Server and schedule a weekly backup.

It's hella easy.

Use SQL Server Developers ($49!) tool to setup the maintenance plan on
the MSDE server to do this.

-Aaron

 

[Guest]

I agree with Douglas, it seems that you have more than one user using your
file. It is better if you split it. As long as you are aware that the file is
closed, the vbscript will copy it with no problem. But once again, you should
split your file.