By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,731 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,731 IT Pros & Developers. It's quick & easy.

Backing up an Access mdb file - how best to automate?

P: n/a
MLH
I'm developing an app that I'd like to backup each time I open it for
modifications. Is it too late to do so after opening the file with
Access? Here's what I've been trying... Trevor Best posted
this code in 1995...

On frmMainMenu, I have a Fn that I launch with a button. Here
is the function:

Sub CopySingleFile (szSrc As String, szTgt As String)

Const BYTE_SIZE = 20480
Dim hfInput As Integer
Dim hfOutput As Integer
Dim lFilePointer As Long
Dim lRemain As Long, lFileLen As Long
Dim szBuffer As String
On Error GoTo CopySingleFileError

hfInput = FreeFile
Open szSrc For Binary Access Read Shared As #hfInput
hfOutput = FreeFile
Open szTgt For Binary Access Read Write Lock Read Write As
#hfOutput
'Open file [For mode] [Access access] [lock] As [#]filenumber [Len
= reclen]
lRemain = LOF(hfInput)
lFileLen = lRemain

lFilePointer = 1
Do Until lRemain < BYTE_SIZE
szBuffer = Input(BYTE_SIZE, #hfInput)
Put #hfOutput, lFilePointer, szBuffer
lRemain = lRemain - BYTE_SIZE
lFilePointer = lFilePointer + BYTE_SIZE
lBytesCopied = lBytesCopied + BYTE_SIZE
Loop
szBuffer = Input(lRemain, #hfInput)
Put #hfOutput, lFilePointer, szBuffer
lFilePointer = lFilePointer + lRemain

CopySingleFileExit:
On Error Resume Next
Close #hfInput
Close #hfOutput
Exit Sub
CopySingleFileError:
Select Case MsgBox(Error$(Err), 48 + 2, "Error " & Err)
Case 3
Error Err
Case 4
Resume
Case 5
Resume CopySingleFileExit
End Select
End Sub
I have a button on the main menu that runs

Call CopySingleFile("c:\cr\MyFile.mdb", "c:\MyBak.mdb")

when I click it. However, I get an error #70
(permission denied) when I click the button.
Is something wrong with my logic? Should I
be using CopyObject to recreate the database
and all its objects instead?

Thx
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I back up my production database four or five times a day. First I close it
and it runs the compact thingy on close. I then have this $20.00 program
called ZipFiler that I have setup to backup to a local drive and to backup
to a network drive. Usually takes about 15 seconds to backup to each
location. As far as I know there isn't a method to backup an Access database
either on opening or closing. Though on second thought you could probably
run a batch file that zips it up before opening.

I recommend you check out the Zipfiler program (I have shares (:<) ).
http://www.zipfiler.com/

"MLH" <CR**@NorthState.net> wrote in message
news:lk********************************@4ax.com...
I'm developing an app that I'd like to backup each time I open it for
modifications. Is it too late to do so after opening the file with
Access? Here's what I've been trying... Trevor Best posted
this code in 1995...

On frmMainMenu, I have a Fn that I launch with a button. Here
is the function:

Sub CopySingleFile (szSrc As String, szTgt As String)

Const BYTE_SIZE = 20480
Dim hfInput As Integer
Dim hfOutput As Integer
Dim lFilePointer As Long
Dim lRemain As Long, lFileLen As Long
Dim szBuffer As String
On Error GoTo CopySingleFileError

hfInput = FreeFile
Open szSrc For Binary Access Read Shared As #hfInput
hfOutput = FreeFile
Open szTgt For Binary Access Read Write Lock Read Write As
#hfOutput
'Open file [For mode] [Access access] [lock] As [#]filenumber [Len
= reclen]
lRemain = LOF(hfInput)
lFileLen = lRemain

lFilePointer = 1
Do Until lRemain < BYTE_SIZE
szBuffer = Input(BYTE_SIZE, #hfInput)
Put #hfOutput, lFilePointer, szBuffer
lRemain = lRemain - BYTE_SIZE
lFilePointer = lFilePointer + BYTE_SIZE
lBytesCopied = lBytesCopied + BYTE_SIZE
Loop
szBuffer = Input(lRemain, #hfInput)
Put #hfOutput, lFilePointer, szBuffer
lFilePointer = lFilePointer + lRemain

CopySingleFileExit:
On Error Resume Next
Close #hfInput
Close #hfOutput
Exit Sub
CopySingleFileError:
Select Case MsgBox(Error$(Err), 48 + 2, "Error " & Err)
Case 3
Error Err
Case 4
Resume
Case 5
Resume CopySingleFileExit
End Select
End Sub
I have a button on the main menu that runs

Call CopySingleFile("c:\cr\MyFile.mdb", "c:\MyBak.mdb")

when I click it. However, I get an error #70
(permission denied) when I click the button.
Is something wrong with my logic? Should I
be using CopyObject to recreate the database
and all its objects instead?

Thx

---
This email from Barry Wright has been scanned using AVG 6.0 and is certified
Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.541 / Virus Database: 335 - Release Date: 11/15/03
Nov 12 '05 #2

P: n/a
On Sun, 16 Nov 2003 14:34:07 -0500, MLH <CR**@NorthState.net> wrote:
I'm developing an app that I'd like to backup each time I open it for
modifications. Is it too late to do so after opening the file with
Access? Here's what I've been trying... Trevor Best posted
this code in 1995...


One option you might take a look at is to SaveAsText all of the
objects when you open the db. Although if you open a lot you are going
to have a *lot* of backups; those will have to be managed somehow.
Application.

SaveAsText is an undocumented function that will save your objects as
text. To get information about the arguments for it open the object
browser and right click on it somewhere and select "Show Hidden
Members" then you can run it from the debug window.

Application.SaveAsText

Another function LoadFromText will restore the objects.

Lyle Fairfield has posted code about backups using this approach,
although I believe it was for an ADP. It should be no problem to
convert to MDB. Search Google groups for that.

- Jim
Nov 12 '05 #3

P: n/a
MLH
Thx, guys, for the suggestions. I took the easy way out...
Anytime I start my production database, the following code
runs...

On Error Resume Next
DoCmd Hourglass True
Dim I%, Tmp, MyDb As Database

Kill "MyDB.bak"
Set MyDb = DBEngine(0).CreateDatabase("MyDB.bak", DB_LANG_GENERAL)
Set MyDb = DBEngine(0)(0)
For I = 0 To MyDb.TableDefs.count - 1
Tmp = MyDb.TableDefs(I).name
If Mid(Tmp, 2, 3) <> "Sys" Then DoCmd CopyObject "MyDB.bak", Tmp,
A_TABLE, Tmp
Next I
For I = 0 To MyDb.QueryDefs.count - 1
Tmp = MyDb.QueryDefs(I).name
DoCmd CopyObject "MyDB.bak", Tmp, A_QUERY, Tmp
Next I
For I = 0 To MyDb.containers("Forms").documents.count - 1
Tmp = MyDb.containers("Forms").documents(I).name
DoCmd CopyObject "MyDB.bak", Tmp, A_FORM, Tmp
Next I
For I = 0 To MyDb.containers("Reports").documents.count - 1
DoCmd CopyObject "MyDB.bak", Tmp, A_REPORT, Tmp
Tmp = MyDb.containers("Reports").documents(I).name
Next I
For I = 0 To MyDb.containers("Scripts").documents.count - 1
Tmp = MyDb.containers("Scripts").documents(I).name
DoCmd CopyObject "MyDB.bak", Tmp, A_MACRO, Tmp
Next I
For I = 0 To MyDb.containers("Modules").documents.count - 1
Tmp = MyDb.containers("Modules").documents(I).name
DoCmd CopyObject "MyDB.bak", Tmp, A_MODULE, Tmp
Next I
DoCmd Hourglass False: Beep

The code runs when I launch the app. So if, during a braindead
moment, I inadvertently blow away an important object or trash
something that I worked on all day yesterday, I can take comfort
in knowing that I have a .BAK copy of the app as it was immediately
before starting my mods to it this morning. Works for me.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.