Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scrambled Record (MS Access 2003)

    I have several shared access aps running on a LAN server. Occasionally (fortunately not often) a record will shift in a table. It appears that only a portion of the data in first field gets posted then the remainder of the data goes to the next field and continues in this fashion across the entire record. As you can imagine it will really scramble a record. Two things are especially a problem. One being date fields, when a date field is scrambled it locks up the sort order capability for the table. That of course dominos to the queries that power the forms. Which is when I get a call telling me that the application is down. The second doesn't happen in each instance the auto number field gets whacked usually as a long negative number. Any guesses on what causes this? And most importantly how to prevent it. It only happens to one record and only about once every three or four months.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Scrambled Record (MS Access 2003)

    Bill,

    I too have experienced this, and have not known the cause. In my case, it only affects one record and not preceeding records nor records entered after. When it occurs I have to run queries to append all noncorrupted records into empty tables, and am not able to save the corrupted record. I too hope someone has a solution.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrambled Record (MS Access 2003)

    Autonumbers can legitimately be negative if the New Values property is set to random. And a random autonumber can as long as a long integer can hold, postive or negative, which is a good-sized number. I'm not sure what you mean by it only happening to one record. Do you mean the same record each time or that it only happens to one record at in any particular instance, never to several at a time? Is data being appended to the table from elsewhere at times? If it came from a table with a random autonumber setting, it could still be appended to a table with an increment setting. Random autonumbers are frequently used in applications over a network, since two users are unlikely to attempt to add the same autonumber at the same time that way.

    The first problem also sounds like it might be the result of appending a record from somewhere like an Excel spreadsheet without removing things like tabs and carriage returns from the contents first. I've also seen that kind of thing when bringing data in from database products that allow multiple values to be stored in a single field.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Record (MS Access 2003)

    The records are entered/edited right into the table from an input form. The input and edit forms are powered by queries limited by date criteria. You are correct in your assumption that it only happens to one record at a time. Part of the problem is I don't know when the record gets scrambled. Whether it happens during the initial input of the record or when it is edited later. I am not concerned with the actual value of the auto-number field, it is just puzzling that a subset of the scrambled records have that field affected. When it isn't I use the auto-number field to sort the record so I can get some sort of time line on when the record was initiated. That information has not been of any help.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrambled Record (MS Access 2003)

    Me too.
    When this happens I usually just cut the record out of the table and recreate it. At first I thought I had to redo the whole table as you suggest, but I found that if I can delete the record the rest of the table settles down quite nicely.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scrambled Record (MS Access 2003)

    When I experienced this I was able to correct it by setting the sharing level to 'edited record'.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •