Scrambled Record (MS Access 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

When I experienced this I was able to correct it by setting the sharing level to ‘edited record’.

Reply | Quote