mdb backup

[Ron]

Hi,

Is there a way that I can backup the mdb file from VBA. I only need a simple
file copy routine to execute when a button is clicked.


Ron.

 

[Alex Dybenko]

Hi,
to copy same mdb - look at:
http://www.mvps.org/access/api/api0026.htm

if you have splitted database - you can look at this one:
http://www.pointltd.com/Downloads/Details.asp?dlID=49

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Ron]

Thanks Alex

Hi,
to copy same mdb - look at:
http://www.mvps.org/access/api/api0026.htm

if you have splitted database - you can look at this one:
http://www.pointltd.com/Downloads/Details.asp?dlID=49

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[slymeat]

Is there a way to alter the code at

http://www.mvps.org/access/api/api0026.htm

so that it allows the user to change the filename and directory to where the
back-up is to be saved rather than automatically save it (with the "Copy of"
Prefix) to the directory where the original file is located? i.e. it would
open a File Save As Dialog box

Andrew

 

[Douglas J Steele]

Try replacing the line of code

strSaveFile = CurrentDb.Name

with a call to the Windows File Save dialog (see
http://www.mvps.org/access/api/api0001.htm at "The Access Web" for details)

 

[slymeat]

Thansk for the reply

everyhting works fine except when I click the existing back-up file that I
want to replace, the code ask me to I want to replace the existing file. I
click yes but the code puts "Copy of..." in front othe file name and saves
the file to the target directory. If I try it again, I get the "Copy (2)..."
prefix put on the file name. How can I force the code to accept the name
given rather than putting "Copy of" in front of the file name?

 

[Douglas J Steele]

What did you change the code to?

If strSaveFile contains a full path to a file, that's what the copied file
should be named.

 

[slymeat]

I didn't change the code and I checked the full file pathname that was being
generated and it's correct but I noted that the article at
http://www.mvps.org/access/api/api0026.htm

does say at the top of the article that it puts the prefix "copy of" in
front of the file name as it was originally designed to be an automatic
back-up function to put a copy of the database in the same directory as it is
in. Would this be causing the problem?

 

[Douglas J Steele]

If you look at that code, it's using exactly the same value for .pFrom and
..pTo when populating tshFileOp. That's why the "copy of" appears.

You asked how to change the code to get around that, and I told you how (set
strSaveFile to something else).

 

[slymeat]

Sorry Douglas

I wasn't clear with my last post, I have changed the code so that the
strSaveFile string is set by the result of the call to the function that uses
"File Save As" window. I have checked what the resulting string is and it
correctly identifies the full pathname of the file (in the target directory)
that I click on to replace (and which I am asked whether I want to replace).
However, even though I select the option that the file be replaced, it makes
a new file with the prefix "Copy of...." rather than replacing the existing
file. I must be missing something blindingly obvious but am a self-thought
novice!

 

[Douglas J. Steele]

Just to make absolutely certain, after the code:

With tshFileOp
.wFunc = FO_COPY
.hwnd = hWndAccessApp
.pFrom = CurrentDb.Name & vbNullChar
.pTo = strSaveFile & vbNullChar
.fFlags = lngFlags
End With

put

MsgBox "About to copy " & tshFileOp.pFrom & _
" to " & tshFileOp.pTo

Is it what you expect?

 

[slymeat]

I've put in the code that you suggested to test what the tshFileOp.pFrom &
tshFileOp.pTo values are. Just to explain the, the name of the file I'm
copying is Filing.mdb and it's on the C drive directly. For test purposes,
I'm trying to copy it to My Documents

When I run the code with the additional line you suggest, I go through the
replace file prompt etc etc and get the text:
"About to copy C:\Filing.mdb" and nothing more, no "to ...."

I took out the "& vbNullChar" from after "CurrentDb.Name" and "strSaveFile"
in the "with.." section and ran it again, this time I get the text:

"About to copy C:\Filing.mdb" and nothing more, no "to C;\Documents and
Settings\acunningham\MyDocuments\Filing 2000.mdb"

The strSaveFile appears to be correctly set at the destination filename and
path but I'm still getting a final result of "Copy of Filing 2000.mdb" being
put into My Documents!

 

[slymeat]

Sorry - there was a mistake in my last post, what I meant to say is:

I've put in the code that you suggested to test what the tshFileOp.pFrom &
tshFileOp.pTo values are. Just to explain the, the name of the file I'm
copying is Filing.mdb and it's on the C drive directly. For test purposes,
I'm trying to copy it to My Documents

When I run the code with the additional line you suggest, I go through the
replace file prompt etc etc and get the text:
"About to copy C:\Filing.mdb" and nothing more, no "to ...."

I took out the "& vbNullChar" from after "CurrentDb.Name" and "strSaveFile"
in the "with.." section and ran it again, this time I get the text:

"About to copy C:\Filing.mdb to C:\Documents and
Settings\acunningham\MyDocuments\Filing 2000.mdb"

The strSaveFile appears to be correctly set at the destination filename and
path but I'm still getting a final result of "Copy of Filing 2000.mdb" being
put into My Documents!

 

[slymeat]

By the way, the code I'm using to call the File Save As Dialog is:

strSaveFile = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

which links to the code that you suggested I use at:
http://www.mvps.org/access/api/api0001.htm

 

[Douglas J Steele]

I find that I have to comment out the line ".hwnd = hWndAccessApp". If I
don't, I get a "Microsoft Access has encountered a problem and needs to
close. We are sorry for the inconvenience" error box. However, obviously
you're not encountering that problem, or you would have mentioned it...

I have found an error in the code. Technically, where it's assigning values
to .pFrom and .pTo, it should be appending vbNullChar & vbNullChar, not
simply vbNullChar. However, even with that error, it still works for me
(after commenting out the line)

I'm at a loss for what else to suggest. I suppose that once the copy has
completed, you could use the VBA Name function to rename the file:

If Len(Dir("C:\Documents and Settings\acunningham\MyDocuments\Copy of Filing
2000.mdb")) > 0 Then
Name "C:\Documents and Settings\acunningham\MyDocuments\Copy of Filing
2000.mdb" _
As "C:\Documents and Settings\acunningham\MyDocuments\Filing 2000.mdb"
End If

but that shouldn't be necessary!

 

[slymeat]

Where you getting the same outcome? - i.e. the file that was being produced
was prefixed with "Copy of " or is that fault just happening forme?

 

[Douglas J Steele]

No, the copied file had the name I gave it.

 

[slymeat]

Would it be possible for you to post your code so that I can check if there's
any minor differences with mine. That's my last effort to resolve the problem
and thanks for the help to-date!

 

[Douglas J Steele]

Sorry, but I didn't save the test module. It was simply the code from the
web page. All I did was comment out the line I indicated, and hard-code both
..pTo and .pFrom.

 

[Lateral]

Hi Alex,

I have implemented your "mdlBackUpSolution" solution and it works almost 100%!

I have the following problem:

1. If I add a menu item called "Backup" to my SwitchBoard that runs the
"mcBackup" macro, an error messages is displayed saying that the database is
open. If I include the "mcBackup" macro in the Autoexe macro I get the same
error message......

What am I doing wrong?

Thanks
Regards
Greg