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

#1 (**permalink**) 28-Jul-2006, 08:16 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10843-can-one-prevent-access-2000-table.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
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

#2 (**permalink**) 28-Jul-2006, 08:16 AM

David Okuni wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
> and copy all the records into it, except the nonesense record. This
> is cumbersome, some when there are thousands of records. Sometimes I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
> 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.

--
Joseph Meehan

Dia duit

#3 (**permalink**) 28-Jul-2006, 08:16 AM

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

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"David Okuni" wrote:

> Lately, whenever a small Microsoft Access 2000 database at a hospital
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
> 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

#4 (**permalink**) 28-Jul-2006, 08:20 AM

David Okuni Okuni@discussions.microsoft.com> wrote:

>Lately, whenever a small Microsoft Access 2000 database at a hospital
>undergoes a stress due to computer (or Access) crash , a nonesense record
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10843
>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