data conflict?

[PayeDoc]

Hello All

I have a split mdb with backend on a server and frontends on 6 PCs. The
backend is copied every 10 minutes to a backup directory, and then renamed,
such that the backup directory always holds 8 copies of the backend, taken
at 10 minute interval (this is done with windows scheduler running a simple
cmd file): so we can if necessary go back to any one of them if we get any
problems (like occasional 'unrecognized database' or other corruption -
which has happened rarely, but often enough!).

The problem is that the copy process seems occasionally to cause an append
query to miss one record when it is run - i.e. one fewer records is appended
than should be. This particular append query is central to our process, and
is run by one user or another every 5 minutes or so. The 'missing record'
problem occurs once or twice a day when the office is busy, and I can think
of nothing else that is causing it.

Can anyone suggest another way of creating the backup files without
affecting the append query (and I'm sure there are other potentially data
issues)? I'm guessing that, short of converting to sqlserver, there is no
way: but I'm often amazed by the ingenuity that is displayed in these
newsgroups!

Hope someone can help.
Many thanks
Les

 

[Jeff Boyce]

Are you saying that the automatic backup routine does NOT chase everyone out
before making the copy? If so, what would happen if someone was in
mid-stride when the backup ran? Wouldn't that make for the possibility that
something wasn't completed?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Leslie Isaacs]

Hello Jeff

Thanks for your reply.

No, the backup routine doesn't chase everyone out - that would be
impractical for us. I had hoped, no doubt naively, that access only really
writes data to the database as the record is exited, or refreshed, or
whatever, and that otherwise, while a user was 'editing' data on a form that
data was only in the frontend. I had also thought that the actual moment of
writing the data to the backend would be extermely brief, and that therefore
the chances of any coincident writing and saving would be remote: and that
if/when it did happen a message would say "Wait". I guess I was wrong!

Is there a better way? As I said, forcing everyone out every 10 minutes just
isn't practical.

Hope there's a way forward here, though!
Thanks again
Les

 

[Rick Brandt]

Is there a better way? As I said, forcing everyone out every 10 minutes
just isn't practical.

If your data is so critical that you feel you need to make backups while
people are actually using it then you need a server database.

If you must stay with a Access/Jet back end change your backup to once a
night when it is not being used.

 

[Paul Shapiro]

FMS used to have an Access backup product, and probably still does but I
haven't looked in some years. You can schedule online Access backups. I seem
to remember it does the backup with sql statements, copying data from the
live tables into the backup db, so it can run successfully while users are
in the system.

If you want to stay with file copy backups, you can use a batch command file
that only makes the copy if the .ldb file does not exist. You may get less
frequent backups, but you won't run the current risk of having corrupted
backup files and interference with users' data operations.

I would agree with the others that if you really care about backups every 10
minutes you would be better off using SQL Server for the data store.

 

[Jeff Boyce]

I'm with Rick...

If you believe the data is so critical that you can't afford even an hour's
loss, let alone a day's loss, get a server and use something like SQL
Server.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.