Replacing backend database

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,
At work, on the frontend database, I have a command button which uses Winzip
to zip the backend database and copy it to a floppy. The user can then take
this floppy to their home computer and update their home version of the
backend database.

The problem is that Winzip will not unzip from the floppy and replace the
existing home backend because that file is in use (the database is open with
its linked tables). The command line is as follows:

cmdstring = "C:\Program Files\Winzip\winzip32 -e -o a:\fpsdata.zip
C:\Access97\". (The "-e" is to extract; the "-o" is to overwrite).

I suspect I wouldn't have any problem if I were using Pkzip, but then I
would have to have that installed on all the users' home computers.

I'm thinking that a workaround is to unzip the file from the a-drive to
another folder and then using the Windows File Copy command to copy and
replace the actual backend -- hoping that Windows would let me replace the
(open) backend database even though Winzip wouldn't.

Questions:

What is the syntax for the Windows File Copy command, and is there a better
method for automating this process? (I hate having to walk users through
this process each time using Windows Explorer!).

Thank you.
Mark
 
Yes, using the Windows interface (Windows Explorer) allows me to copy and
replace the backend database even though the database is in use. (However,
I do have to first close a hidden form bound to the backend database which
is used at work to keep the network connection open)
..
 
Mark,

If you have VB you can make a small .exe that does the file work and then
launches the database. If you don't, you can even use a plain old-fashioned
command prompt batch file to do the same... they still do the job!

Whichever way you go, I would suggest you add an extra line in there to make
a backup copy of the back end on the home PC before the newer version from
the floppy overwrites it... floppies are floppies. Actually, I'm surprised
you still use them. You should consider USB flash disks; once you've tried
one, you won't be able to live without it!

HTH,
Nikos
 
Thanks Nikos for the response. However, do you know the VB code to do a
Windows copy command from within the database? (I'll want to replace the
existing file without prompting).
Thanks,
Mark
 
Hi,
I'm interested in how you did this.
I've just posted asking how I can have a button in a form that backs up my
database and was told it can't be done (as I've found out - I can't copy an
open file)
See thread
How did you manage to copy (pretty much the sanme as backup) via winzip, the
database that was doing the copying?
Howard

Copy of a bit of my thread below.............
I'd like to have a button (with a caption of Backup!) make a complete copy
of the current database, i.e. the one that has the button in it. However,
when the database is open and my button showing I cannot copy it and when it
is closed and I can copy it, I obviously can't see the button.
Can it be done?
Click to expand...


Whether it can be done or not, you shouldn't do it. An open
file (especially a database) is in an unknown state and
copying it may produce an unusable file.
 
VBA code

Filecopy sourcefile destinationfile

will copy a file from one location to another. But you are missing the
point. Its very dangerous to copy an open database.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
The following code uses Winzip to copy and zip the backend database located
on "P:\" to a floppy. I certainly appreciate the warnings from the other
contributors to this thread. I've been using this for awhile and haven't
run into any problems. I suspect that it would be particularly vulnerable
if another user was editing the database at the time of copying (??)

Private Sub Command55_Click()
' zips and copies main database from office computer to a floppy.
Dim cmdstring, foo
cmdstring = "C:\Program Files\Winzip\winzip32 -a a:\fpsdata.zip_
P:\fpsdata.mdb"
DoCmd.Close
foo = Shell(cmdstring, 1)

End Sub
 
Thank you Adrian.
Is there anyway to minimize the danger -- are there particular things to
watch out for? I need a SIMPLE way to have a couple of users update their
home databases every couple of weeks just enough for them to be able to do
some work at home, but without going through a complicated replication
process. From past experience, either I would have to do the copying for
them, or risk them copying over the main database at work! Mark.
 
I havent done this from within Access, but the problem is that you MUST
close the active database before attempting to copy it. And of course when
you close it, all the code stops running. I think you have three options.

If you want to stay in Access, have the users open another database, called
Backup, or something obvious, with just a single form in it, and a button on
it which does the filecopy on the database you want to copy/backup. Of
course you can add Restore and other facilities as well.

Use VisualBasic or some other program to create a stand-alone app to do the
copy, and place a shortcut on the desktop. Even just a batch file would
work for this.

Educate your users to use the Windows file copy/paste - its not that hard,
after all.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Adrian,
Perhaps I'm wrong, but it seems to me that both Windows and Winzip (which is
probably the same entity) have built-in protection against copying an open
database -- neither program allows the copying from, or to an *open*
database.

I've noticed, however, that in the case of a split database each time I open
a form bound to one of the attached tables, then the .ldb file is created
for the backend database. Under this condition, file-copying of the backend
database cannot occur. As soon as I close the bound form(s), however, the
..ldb file is deleted and both Windows and Winzip allow copying to, and from
that backend database.

I'm guessing that for all practical purposes, if there are no open forms
bound to the backend database then regardless of whether the frontend
database is open or not, the backend database is not in use and, therefore
it's like your one recommendation of opening a separate database to do your
copying, replacing, compacting, etc. of the backend -- only, in this case,
it's the frontend database fulfilling this role. This makes it simpler for
the user when they want to replace their home backend database from a zipped
copy they bring home on a floppy. They can do this from within their
regular (frontend) database, provided that the code first closes all bound
forms. Thus, I don't have to supply them with a separate database, or
..exe/batch file, or remind them to first close their regular database, or
teach (and re-teach, and re-teach) them how to use Windows Explorer, Winzip,
etc.

At work, the code does not allow copying *to* the (networked) backend, only
copying from it to a floppy. This seems to work fine even without closing
the bound forms. I haven't tried it while someone was actually editing a
record -- perhaps this would result in a corrupted file copied to the
floppy, but I'm thinking that it wouldn't harm the file being copied from.
Mark.
 
Yes, if you have a split database, you can certainly close all bound forms.
This should effectively close the backend, and then copy it. In fact you
could probably get away with just disconnecting the backend in code,
although the code to do this is more complex than just closing all the
forms.

I would still not attempt to copy from any open database. There is a *lot*
of data stored in memory buffers, and not necessarily in synch between the
buffers and the disk image. So you could end up with corruption, at least
in principle. Unfortunately you could test this maybe a hundred times, and
it works ok, then falls over catastrophically when one of your users does
it. Seems not worth the risk to me. To be certain, I would check for the
existence of the .ldb file, and only copy when a dir read showed that did
not exist.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top