Database backups and other questions

[dhstein]

Until now, the database I'm developing has been one which only I update and
the users just read information. So I keep the main copy (unsplit) and
distribute a new version (splitting the database) when I do updates. This
allows me to easily backup the database and ensure that there are no data
corruption problems. Pretty soon, there will be a need for another person to
update one of the tables. So I have 2 choices.
1) Keep the split database on the network and do regular backups - probably
the recommended solution.

2) Provide a second database with the one table that gets updated and
periodically import the updated data into the original database.

I would like to use option 1. But I'm concerned about Access corrupting the
database as well as other issues like record locking for multiple users
updating at the same time. So what is the track record for Access and table
corruption. Is this a non-issue ? Can I back up an open database or does it
have to be closed (users might leave the application open when the backup is
running) What special handling do I need when I open a recordset if multiple
users will be updating? Please let me know what experiences you've had with
these types of issues. Thanks for any advice.

 

[Tom van Stiphout]

On Thu, 12 Feb 2009 19:23:02 -0800, dhstein

On a stable network corruption is VERY RARE. We have clients with
corruption only under extreme conditions (lightning strike - power out
in the entire subdivision).
Multiuser conflicts are also very rare, except in extreme conditions
(when we can still program against that).
You backup an open database at your peril. It will work 99% of the
time. Is that good enough?

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

Until now, the database I'm developing has been one which only I update and
the users just read information. So I keep the main copy (unsplit) and
distribute a new version (splitting the database) when I do updates. This
allows me to easily backup the database and ensure that there are no data
corruption problems. Pretty soon, there will be a need for another person to
update one of the tables. So I have 2 choices.
1) Keep the split database on the network and do regular backups - probably
the recommended solution.

2) Provide a second database with the one table that gets updated and
periodically import the updated data into the original database.

I would like to use option 1. But I'm concerned about Access corrupting the
database as well as other issues like record locking for multiple users
updating at the same time. So what is the track record for Access and table
corruption. Is this a non-issue ? Can I back up an open database or does it
have to be closed (users might leave the application open when the backup is
running) What special handling do I need when I open a recordset if multiple
users will be updating? Please let me know what experiences you've had with
these types of issues. Thanks for any advice.

As Tom says, you're borrowing trouble.

A properly split database on a stable LAN is very unlikely to corrupt, if you
take reasonable precautions. Having ten or twenty users concurrently updating
it doesn't particularly affect this conclusion.

Backing up an open, active database is VERY likely to cause the backup to be
corrupt, and would be most unwise.

Sure, corruption happens... it's rare. Backups ARE essential (and must be made
when the database is not in use).

 

[dhstein]

Tom, John,

Thanks for your replies. It isn't that I want to back up an open
database. Right now I do backups all the time, but because of the way it
works right now, I have complete control of the main database and since the
users are working from a copy of the main database, if they have it open it
has no effect. But if I move to the multi-user version, then I have to run
an automatic nightly backup and have to figure out how to get the users to
close things down when they go home for the day.

On the other issue, you mentioned that it is possible to program against
multi-user conflicts. What do I need to do for that? I assume there may be
a different way to open a recordset? Or check something before doing an
Update ? Thanks for your help.

 

[Tom van Stiphout]

On Fri, 13 Feb 2009 03:35:01 -0800, dhstein

There is a KB article about automatically closing an Access
application after a certain period of inactivity. It is not foolproof.
A corporate culture of "shut off your machine when you leave for the
day" is probably going to work better. And is better for other reasons
as well.

To program for multiuser conflicts: a more sophisticated error handler
is a good place to start. For example here is some pseudo-code for
running any Action query:
public function RunActionQuery(byval strQuery as string, byval
aryParams as Variant)
on error goto ErrHandler
dim intRetryCount as integer
dim qd as dao.querydef
set qd=g_dbApp.Querydefs(strQuery)
'TODO:Add the parameters
qd.Execute dbFailOnError
ExitHandler:
set qd=nothing
exit function
ErrHandler:
if err.number = 12345 then 'TODO: Replace with the real error
number(s)
'it's a multiuser error.
if intRetryCount < 10 then
'Wait a little bit, and silently retry
intRetryCount=intRetryCount+1
Sleep intRetryCount*50
Resume
else
'We tried long enough. Ask the user if we need to try again.
If MsgBox(Err.Description & " Want to retry some more?",
vbQuestion or vbYesNo) = vbYes then Resume else resume ExitHandler
end if
else
Msgbox Err.Description
resume ExitHandler
end if
End Function

-Tom.
Microsoft Access MVP

 

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

bullshit, corruption happens all the time.

and when you say 'stable network' you don't include any sort of VPN,
Wireless, WAN, etc'

ADP / SQL Server works _GREAT_.

SQL Server supports backups while people are using the database.
SQL Server supports ACID transactions.
SQL Server supports rollback of partially committed transactions.

Jet does not.

Grow some balls, kid-- if you care enough about your data to take
backups-- then move to a database server.