Corrupt Database

  • Thread starter Thread starter Susan
  • Start date Start date
S

Susan

I have an Access DB that links several tables through
ODBC Connection and 1 table stored in Access. This DB is
used weekly by more or less 35 users. Every week around
the same time 1:00 to 1:40pm the DB loses network
connection creating a backup file and locking the DB
until every one exits so the DB can open exclusively to
repair.

I can not control the network but can I change some
settings within the DB like making the ODBC refresh
interval 3 minutes instead of 25. Will that just
increase traffic on the network and cause more problems.
 
I fought this issue for years.

I assume you are using a standard Access database (.mdb)
and not and Access project (.adp). If this is true, you
will constantly fight this issue (connection and
corruption) with Access .mdb files.

THE best solution is to switch to SQL. If you have
Access 2000 or later, a "free" version is included using
the MSDE data engine instead of Jet (the database engine
that runs Access). I cannot emphasize enough how much
happier you will be with MSDE and Access projects than
Jet when you are in a network environment. MSDE
is "optimized" for 5 concurrent users, but will work with
more than you have. If more than 5 are concurrenty doing
something it will slow down, but my experience is that it
is still faster than Jet.

There is something you can do with jet to help.
Microsoft has several articles about Opportunistic
Locking in Jet that will almost certainly help. You will
be required to update all users of Access to the latest
service pack and if the back end is on a server it will
also need to be updated. Than you will need to go into
the registry of all clients and the server and add/change
the keys referenced in the referenced article.

This is about locking and fixed several corruption issues
for me.

http://support.microsoft.com/default.aspx?scid=kb;EN-
US;296264

I also suggest you google on - White paper Microsoft Jet
MSDE - for a lot of info.

Finally, the learning curve on SQL is steep, but it is
worth every bit of it.
 
Every week around
the same time 1:00 to 1:40pm the DB loses network
connection
Click to expand...

Access/ Jet are very sensitive to network performance, and loss of network
connections are likely to lead to file corruption and data loss.

Thirty five users is pushing it a bit for Jet: how many of these are
concurrent?

Note too about the use of MSDE is that it is "optimised" for five
concurrent connections -- read that as limited to five, but then again you
can make and drop connections really quickly to make it serve more users.

HTH


Tim F
 
Randy

Thank you for the info. The linked tables are from an
oracle db and I'm not familar with the environment if the
back engine was MS SQL I would be Ok. My plan is to link
the tables I need from Oracle to SQL server and use
another front engine asp.net or vb.net (more reading and
testing)

Again Thanks

Susan
 
Since you linked tables are already using a industrial strength database,
then the advice changing to server is not needed, since you already done
that!

The problem you are having is that your ms-access database setup is ALL
WRONG!

YOU MUST give each user a copy of that front end. In other words, your
application that you wrote in ms-access MUST BE installed on every pc. You
always installed all other appcltions like Word, Excel, etc. So, when you
write your own applications, then again you must install it on EACH PC.

DO NOT allow multiple users into the front end, and you corruption problems
will BE GONE FOREVER!

I sure from time to time the Oracle people must think that the ms-access is
a piece of junk because it corrupts! In fact, the problem here is the person
who setup the database has done it all wrong, and that is the real problem
here.

So, give each user that front end (preferable a mde), and this problem is
gone...
 
Back
Top