Access backup

[Martin]

In our office we have our Access database saved on a server. Four people use
this database. Each person has a shortcut on their desktop to open the
database. No-one tells the database to backup--but every once in a while it
backs up on its own and names the backups db1.mdb, db2.mdb etc. How do we
get this automatic backup to stop?
When an automatic backup is made, new shortcuts to the newest backup need to
be made.

 

[George Hepworth]

This is actually more likely to be a result of one or more incomplete
"Compact and Repair" attempts by the database. If an attempt is made to C&R
a database which another user has open, the C&R can't complete and leaves
the temporary db named "db1.mdb" behind. Of course, if it happens again and
there is already an orphaned db1.mdb, then the C&R calls the new temp
db2.mdb and so on.

It does no harm to delete these temp mdbs, but be careful not to mess with
the production mdb itself.

Do you, by any chance, have the option to "Compact and Repair on Close" set
for this database?

On a related note, are all of your users working with a single mdb? Do you
NOT have a split design in place? A split design involves a separate mdb,
usually referred to as the "back end", in which only the tables for your
data reside. In a separate "front end", you should have all of the remaining
objects--forms, queries, reports, code modules and macros.

Each user should have and USE their own copy of the Front End. That
minimizes the problems caused by multiple users working with a single mdb
file.

Letting multiple users work with a single mdb is an open inventation to
corruption.

Search for articles on "Split Database" for more information.

 

[John W. Vinson]

Urgent warning: Split your database, *now*. Sharing a single mdb among
multiple users can and will cause bloat, bad performance, and an increased
risk of corruption. Each user should have their own individual copy of a
"frontend" containing the Forms, Queries, Reports, etc., linked to a shared
Backend containing the tables. See
http://www.granite.ab.ca/access/splitapp.htm for a thorough discussion.

In our office we have our Access database saved on a server. Four people use
this database. Each person has a shortcut on their desktop to open the
database. No-one tells the database to backup--but every once in a while it
backs up on its own and names the backups db1.mdb, db2.mdb etc. How do we
get this automatic backup to stop?

Turn off Auto Compact. It's not really a backup other than in a manner of
speaking; Compacting works by copying the database to db1.mdb (or 2 or 3...),
deleting the old database and renaming the new one back to the original name.

When an automatic backup is made, new shortcuts to the newest backup need to
be made.

Why? I'd just keep a separate backup, independent of the compaction process.

 

[Arvin Meyer [MVP]]

In our office we have our Access database saved on a server. Four people
use
this database. Each person has a shortcut on their desktop to open the
database. No-one tells the database to backup--but every once in a while
it
backs up on its own and names the backups db1.mdb, db2.mdb etc. How do we
get this automatic backup to stop?
When an automatic backup is made, new shortcuts to the newest backup need
to
be made.

In addition to what the others have said, all of which is extremely
important, you should be regularly backing up the data and a copy of the
front-end which should live on the server to replace the local desktop
front-end should it become necessary. Daily incremental backup should be
done, and the entire server should be backed up at least weekly.

 

[a a r o n . k e m p f]

you actually CANNOT back the data up when someone has it open.

This is just another symptom that Jet databases don't fit the needs of
MOST businesses.

 

[a a r o n . k e m p f]

Urgent warning: Upsize your database, *now*. Sharing a any type of Jet
database among
multiple users can and will cause bloat, bad performance, and an
increased
risk of corruption. Each user should have their own individual copy of
an ADP
"frontend" containing the Forms, Reports, etc., linked to a single
shared connection to a
SQL Server Backend containing the tables.

See www.microsoft.com/sql

 

[BruceM]

Please ignore Aaron. He is incorrect about Jet in a multi-user environment.
AFAIK it is possible to back up a Jet back end file that is in use, but
there may be nuances of which I am unaware..

 

[a a r o n . k e m p f]

Bruce;

So sorry that you've never had to backup a Jet database.

Please explain to me how it works, because I've had corruption
failures on busy jet databases _ALL_ the time

 

[BruceM]

There is a daily network backup. The BE files are on the network. I
couldn't say for sure that the BE files are never in use during the backup,
but I do know there have been no corruption problems. I do know that in
order to have a test copy of the data I have copied BE files from working
databases, and have not had problems.

Quite a remarkable thing that you have failures all the time with a product
you never use.

message
Bruce;

So sorry that you've never had to backup a Jet database.

Please explain to me how it works, because I've had corruption
failures on busy jet databases _ALL_ the time

 

[Ron]

In our office we have our Access database saved on a server. Four people use
this database.  Each person has a shortcut on their desktop to open the
database.  No-one tells the database to backup--but every once in a while it
backs up on its own and names the backups db1.mdb, db2.mdb etc.  How dowe
get this automatic backup to stop?
When an automatic backup is made, new shortcuts to the newest backup needto
be made.

WARNING: I'm NOT an MVP so "the regulars" here have probably
forgotten more about Access than I know at this point. But...

I've looked at all the responses and it seems to me that everyone has
overlooked something. The OP said they're changing the shortcuts to
the database (in order to use it, add records etc I would assume) to
this new db1 or db2 or whatever.

Number 1, that's not what a "backup" is for. You don't use the
backup. It's for use only if there's a problem with the original
file. The original file (with whatever name it had originally) should
be the datafile you're users are using, adding to, modifying, etc.

So, to me, it appears you have "incremental" backups (because each
time someone triggers the automatic C & R, it renames it db##.mdb and
everyone switches to that one via their shortcuts) that you're then
switching to and using as your main datafile. Because the compact and
repair operation couldn't do it's work (because another user had the
same file open) isn't this db#.mdb suspect as far as accuracy? After
all, the reason the C & R couldn't do it's job is because someone else
is using the database, perhaps adding or modifying a record--so
wouldn't that mean the recently added, changed data might not be
included in the new db##.mdb?

Me thinks the original poster may have more problems than just the
naming and shortcuts to the file they're using. Perhaps?

ron

 

[BruceM]

As I recall, automatic C&R is performed only when the last user exits the
database, so the problems can't be the result of several users doing C&R at
the same time. The C&R process can fail for any number of reasons, but the
db1 etc. file often functions well enough as a substitute for the original
that it may seem the only difference is the name.

In our office we have our Access database saved on a server. Four people
use
this database. Each person has a shortcut on their desktop to open the
database. No-one tells the database to backup--but every once in a while
it
backs up on its own and names the backups db1.mdb, db2.mdb etc. How do we
get this automatic backup to stop?
When an automatic backup is made, new shortcuts to the newest backup need
to
be made.

WARNING: I'm NOT an MVP so "the regulars" here have probably
forgotten more about Access than I know at this point. But...

I've looked at all the responses and it seems to me that everyone has
overlooked something. The OP said they're changing the shortcuts to
the database (in order to use it, add records etc I would assume) to
this new db1 or db2 or whatever.

Number 1, that's not what a "backup" is for. You don't use the
backup. It's for use only if there's a problem with the original
file. The original file (with whatever name it had originally) should
be the datafile you're users are using, adding to, modifying, etc.

So, to me, it appears you have "incremental" backups (because each
time someone triggers the automatic C & R, it renames it db##.mdb and
everyone switches to that one via their shortcuts) that you're then
switching to and using as your main datafile. Because the compact and
repair operation couldn't do it's work (because another user had the
same file open) isn't this db#.mdb suspect as far as accuracy? After
all, the reason the C & R couldn't do it's job is because someone else
is using the database, perhaps adding or modifying a record--so
wouldn't that mean the recently added, changed data might not be
included in the new db##.mdb?

Me thinks the original poster may have more problems than just the
naming and shortcuts to the file they're using. Perhaps?

ron

 

[Tony Toews [MVP]]

Urgent warning: Upsize your database, *now*. Sharing a any type of Jet
database among
multiple users can and will cause bloat, bad performance, and an
increased
risk of corruption.
False.


Each user should have their own individual copy of
an ADP
"frontend" containing the Forms, Reports, etc., linked to a single
shared connection to a
SQL Server Backend containing the tables.

Almost always quite impractical.

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/

 

[Happy Trails]

Please explain to me how it works, because I've had corruption
failures on busy jet databases _ALL_ the time

Why do you still use it "_ALL_ the time"?

I thought you didn't like it?

Why don't you switch to SQL?

 

[David W. Fenton]

Please ignore Aaron. He is incorrect about Jet in a multi-user
environment.

You could more correctly say about Aaron:

He is incorrect on virtually any subject he chooses to "address."

 

[Nona]

My suggestion, learned in these discussion groups, is to uncheck the option
to automatically Compact and Repair upon closing the database. You can
manually compact as often as you think necessary, but I can almost guarantee
that you won't get the copies named db1, db2, etc. if you uncheck this
option. At least this worked for me!

 

[a a r o n . k e m p f]

Impractical?

I'd bet that 90% of the people here would prefer a platform that
_NEVER_ crashes--
_NEVER_ bloats..
where maintenance is automatic,
performance is better,
it's faster
**and **
easier development?

 

[a a r o n . k e m p f]

David is incorrect.

It's not appropriate for _ANYONE_ and it's not appropriate for
_EVERYONE_.

Jet is crap, it crashes, it bloats-- most people in the real world
know better-- they moved to SQL Server a long time ago

Jet can't handle a half dozen users
and a mere 25mb of data without crashes and bloat

 

[BruceM]

You could end up with the same problem with a manual C&R, but at least you
will see it at the time. That being said, I agree that automatic C&R should
be avoided.

 

[BruceM]

Still wrong about the 25mb limit or the number of users. It is a matter of
proper database design. If you are having problems you should look there.

message
David is incorrect.

It's not appropriate for _ANYONE_ and it's not appropriate for
_EVERYONE_.

Jet is crap, it crashes, it bloats-- most people in the real world
know better-- they moved to SQL Server a long time ago

Jet can't handle a half dozen users
and a mere 25mb of data without crashes and bloat

 

[Arvin Meyer [MVP]]

message

Jet can't handle a half dozen users
and a mere 25mb of data without crashes and bloat

-------------------------------------------

Once again. If you'd learn how to design and maintain a database, you would
never have a problem with crashes or bloat. Jet can easily handle dozens of
users with hundreds of MBs of data. If you want a demonstration, I'd be
happy to give you one, but since they will no longer let you on the
Microsoft campus, that's unlikely.