Programmatically backup back-end database

[Karen Hagerman]

Still a newbie and working on my first deployable database.

I'll be using the Package Wizard to create a setup package.

One of the capabilities I'd like to have in the database is the ability to
backup the back-end data tables from the database. Using just filecopy
doesn't work because the back-end is linked to the front-end database AND
using the built-in backup feature in Access 2003 doesn't work because it
backs up the front-end, not the back-end.

I found this piece of code on the internet

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.copyfile File1, File2
Set fs = Nothing

So........what is the downside of using Scripting.FileSystemObject"?

Karen

 

[Wayne Morgan]

I just tried the following and it worked,

FileCopy "\\computername\sharename\mydatabase_be.mdb",
"c:\test\mydatabase_be.mdb"

If you are using a backend file, it should be open in shared mode. If you
have it open in Exclusive mode, this won't work. The main problem with using
the scripting object is that it is just one more thing that can go wrong and
you don't need it.

 

[Karen Hagerman]

Hi Wayne,

OK, well this is what I was using and I get a permission denied error. I've
checked the back-end database and it is in 'shared' mode and the file itself
is not 'read only'. I really would prefer to use this code, do you see a
problem?

Function DoBackup()
On Error GoTo Err_DoBackup

Dim strNewDBName As String
Dim strOldDbName As String
Dim strYear As String
Dim strMonth As String
Dim strDay As String
Dim x As Integer


strOldDbName = Application.CurrentProject.Path &
"\InvestigatorNerd_be.mdb"

DoCmd.Hourglass True


' get date data for backup file name
strYear = DatePart("yyyy", Now)
strYear = Mid$(strYear, 3, 2)
strMonth = DatePart("m", Now)
If Len(strMonth) = 1 Then
strMonth = "0" & strMonth
End If
strDay = DatePart("d", Now)
If Len(strDay) = 1 Then
strDay = "0" & strDay
End If

strNewDBName = Application.CurrentProject.Path & _
"\Backup\" & "InvestigatorNerd_be_" & strMonth & _
"_" & strDay & "_" & strYear & "Backup.mdb"

' copy database
FileCopy strOldDbName, strNewDBName

DoCmd.Hourglass False
DoCmd.Beep
MsgBox "The database has been backed up"


Exit_DoBackup:
Exit Function

Err_DoBackup:
MsgBox Str(Err)
MsgBox Error$
Resume Exit_DoBackup

End Function

--
Karen
I just tried the following and it worked,

FileCopy "\\computername\sharename\mydatabase_be.mdb",
"c:\test\mydatabase_be.mdb"

If you are using a backend file, it should be open in shared mode. If you
have it open in Exclusive mode, this won't work. The main problem with using
the scripting object is that it is just one more thing that can go wrong and
you don't need it.

 

[Wayne Morgan]

I just tried it again and it appears to work. You may run into a problem if
another user has the file locked for some reason. Also, verify that you have
the appropriate permissions on the directories you're using. I checked to
see if there may be a problem if there are spaces in the path, but that
doesn't appear to be the case. I also tried it with UNC names and that
worked.

You can simplify the generation of your date stamp. Also, I would recommend
placing it in Year, Month, Date order. This will cause it to sort
chronologically by the date in the name when you sort by name.

strDateStamp = Format(Date, "yyyy_mm_dd")

This will result in 2004_05_07.

 

[Wayne Morgan]

Karen,

See if either of these help.
http://www.mvps.org/access/api/api0026.htm
http://www.mvps.org/access/modules/mdl0045.htm

 

[Karen Hagerman]

Hi Wayne,

Thanks for responding again. I noticed that the directory was 'read-only'
so I changed it and still had the same error message. When I re-checked the
directory it was 'read-only' again!

I did a search on filecopy and the following link describes the error
exactly:

http://support.microsoft.com/default.aspx?scid=kb;en-us;172711

So I used that code with some modification and your suggestion for the date
and voila! it works.

Thanks for hanging in there.

--
Karen
Karen,

See if either of these help.
http://www.mvps.org/access/api/api0026.htm
http://www.mvps.org/access/modules/mdl0045.htm