Can one prevent Access 2000 table links breaking during a crash?

[Guest]

Lately, whenever a small Microsoft Access 2000 database at a hospital
undergoes a stress due to computer (or Access) crash , a nonesense record
with some values is inserted in one of the tables, where the ID# is left
blank/null, even though in the design it is a primary key and so should be
unique and not NULL.

This record automatically generated by Access software cannot be removed by
compacting and repairing. Often I've to create a new table and copy all the
records into it, except the nonesense record. This is cumbersome, some when
there are thousands of records. Sometimes I find that a table link
(one-to-many) is broken during one of the crashes, and I've to manually find
and match keys on "many" side with the one on "one" side, including deleting
records on the "many" side.

Is there any design improvement or VBA program one can add to the database
to prevent crash errors resulting in a record being corrupted or new one
inserted?

Thank you.

David

 

[Joseph Meehan]

Lately, whenever a small Microsoft Access 2000 database at a hospital
undergoes a stress due to computer (or Access) crash , a nonesense
record with some values is inserted in one of the tables, where the
ID# is left blank/null, even though in the design it is a primary key
and so should be unique and not NULL.

This record automatically generated by Access software cannot be
removed by compacting and repairing. Often I've to create a new table
and copy all the records into it, except the nonesense record. This
is cumbersome, some when there are thousands of records. Sometimes I
find that a table link (one-to-many) is broken during one of the
crashes, and I've to manually find and match keys on "many" side with
the one on "one" side, including deleting records on the "many" side.

Is there any design improvement or VBA program one can add to the
database to prevent crash errors resulting in a record being
corrupted or new one inserted?

Thank you.

David

There are several way of looking at this. Personally I think you should
start by addressing the crashing, and not the results.

Is this a shared database with more than one persona accessing it at the
same time? If so is it "Split" Trying to share a non-split Access database
over a LAN is asking for problems. The second most common problem are weak
LAN. Access requires a high speed reliable LAN.

 

[Guest]

I have to agree with Joseph Meehan on fixing the network or what ever is
crashing the database. It's better to not have to use the seatbelts rather
than replace them after every wreck. ;-)

While you are fixing the problem table, the entire database might be corrupt
making it more likely to have the same problem in the future. You may have to
import the entire database into a fresh .mdb file.

Below are some resourses for database corruption issues:

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Tony Toews]

Lately, whenever a small Microsoft Access 2000 database at a hospital
undergoes a stress due to computer (or Access) crash , a nonesense record
with some values is inserted in one of the tables, where the ID# is left
blank/null, even though in the design it is a primary key and so should be
unique and not NULL.

Does this happen in one particular table or in many tables? If one
particular table I'm wondering if there is some logic or a form that
starts to add a record but no data is written to the record for a
considerable length of time. For example a record is written and a
date is inserted in a field on the form which causes the dirty record
indicator, a pencil, to be displayed on the left hand side of the
screen.

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