Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by David Okuni, Jul 28, 2006.

  1. David Okuni

    David Okuni
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    How to prevent sikh erosion? Blogs Oct 21, 2015
    Leisure Stomach Protection / Prevention for travelling to India?? Business, Lifestyle & Leisure Oct 18, 2013
    Controversial Panch Pardhani leader arrested; prevented from attending Shaheedi samagam of Bhai Jaspal Singh Hard Talk Mar 30, 2013
    World Women must play greater role in conflict prevention, UN says Breaking News Oct 28, 2011
    Sikh Coalition Bullying Prevention Summit. Register Today! Sikh Organisations Oct 19, 2011

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  4. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    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

    --
    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
    > 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
     
  5. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    David Okuni <David 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
    >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
     

Share This Page