Back up using compression software

[B. Meincke]

I have a subroutine that backs up our database backup before updating it each
day...

strMyDB = CurrentDb.Name
strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
strDest = strPath & "AttdB_BE.mdb"
strDate = Format(Date, "yyyy-mm-dd")
strBkup = strPath & "BE Backups\" & strDate & " (after).mdb"

If Dir(strBkup) <> "" Then Kill strBkup
FileCopy strDest, strBkup
DoCmd.Hourglass False

As the database is fairly large, this is eating up a lot of server space!

Is there any way I can revise the code so that the resulting files are
compressed, using the compression software built into WXP?

Thanks in advance as always!

 

[Daniel Pineault]

1. I hope you realize copying the active database can lead to corruption of
the copied version. Not a reliable method.

2. As for how-to compress a file, take a look at
http://www.granite.ab.ca/access/compression.htm it has several good
ressources on this subject.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

 

[B. Meincke]

Thank you for your reply, Daniel. I will take a closer look at the link in
the morning.

Would you mind providing further explanation as to why you warn against
copying the backend as a method of back up? This is done early in the morning
while the database is not in use. I've been doing this for the past five
years and never had any issue with it...and it has saved my neck a time or
ten to be able to recover from the backed up copies!

What would you recommend as an alternative?

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[B. Meincke]

Again, Daniel, thank you for your reply. I think I can adapt the recommended
code to suit my situation. I would still appreciate a little more information
as to your warning.

And, Jan, have a great trip (?)

 

[Tony Toews [MVP]]

Is there any way I can revise the code so that the resulting files are
compressed, using the compression software built into WXP?

Doing some searching refining keywords as I went along showed a bunch
of related articles at MSDN at
http://msdn.microsoft.com/en-us/library/aa364219(VS.85).aspx

The following page
http://msdn.microsoft.com/en-us/library/aa363849(VS.85).aspx states

"Use the FSCTL_SET_COMPRESSION control code to set the compression
state of a file or directory. This operation also sets the compression
attribute of the file or directory.

Setting the compression state of a file to a nonzero value compresses
the file, using the compression format encoded by the compression
state value. Setting a file's compression state to zero decompresses
the file. These are synchronous operations. The file is compressed or
decompressed immediately when you set its compression state."

Searching using FSCTL_SET_COMPRESSION and visual basic led me to
http://www.visualbasic.happycodings.com/Files_Directories_Drives/code8.html
which should do what you want. Best wishes.

Note that with API calls you *MUST* save the code before running the
calls. API calls with non valid data in the parameters will crash
Access, VB or whatever platform you are running. I'd also suggest
making frequent copies of the FE MDB while working with this code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

1. I hope you realize copying the active database can lead to corruption of
the copied version. Not a reliable method.

Using FileCopy while the BE is open will give you a "Permission
Denied" message and it will fail. I found this out myself and
documented it at
http://msmvps.com/blogs/access/archive/2008/05/10/permission-denied.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Douglas J. Steele]

Using FileCopy while the BE is open will give you a "Permission
Denied" message and it will fail. I found this out myself and
documented it at
http://msmvps.com/blogs/access/archive/2008/05/10/permission-denied.aspx

However, the CopyFile method from FSO will let you copy an open database. As
Daniel states though, it's not a good idea.

 

[Tony Toews [MVP]]

Is there any way I can revise the code so that the resulting files are
compressed, using the compression software built into WXP?

Thanks for asking this question. This may come in useful for my own
purposes in the future. Blogged and my compressions page has been
updated.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

However, the CopyFile method from FSO will let you copy an open database. As
Daniel states though, it's not a good idea.

Ah, thanks for the clarification. I've never used FSO and have no
intentions of ever doing so.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/