I have a rather large database in Access XP. We have a main "CheckIn" form where we check in samples that are given to us to be tested. This form is bound to the "CheckIn" table. Every once in a while a record will become corrupt. I look at the checkin table and where the record is supposed to be (they are differentiated by an autonumber called SampleID ie, 04-73219) the sample ID is a bunch of squares, and other fields are bold vertical lines etc. It is only in this main Table and in order to fix it I have to manually delete the record from the table and then compact and repair both frontend and backend. Does anyone know why this happens? Most recently it happened twice this week and it's only Wednesday. This is a major problem becasue we can't go losing records. If someone does know why this happens, how can I prevent it? Thank you.
Losing/Corrupt Records
- Thread starter geraldcor
- Start date
- Local time
- Yesterday, 22:24
- Joined
- Feb 28, 2001
- Messages
- 30,342
The corrupted records that look like solid blocks and vertical lines might be carriage control characters. You say you use a form, but is it an actual bound data entry form or does it trigger import of a text file to the selected record? Makes a difference in that there are a myriad of text formats to consider. Some of them could include oddball formatting characters.
Last time I saw anything like that, it wasn't an error. It happened because the form's behavior was set wrong with respect to use of tabs to move from field to field and whether it could cross records when you reached the last field. Also, you might wish to check if someone is trying to be "cute" (or lazy)by typing carriage control characters (CR, LF) rather than using tab characters, 'cause that is what it sounds like you are getting. Particularly if it is in a memo field, but a big text field could be just as bad.
Just a suggestion for sh|ts and giggles, but find out what font you are using in the data entry fields. Find it in form design mode, it is in the text-box's format property list. Then call up that exact font name using the Character Map tool. Use Start >> Accessories >> System Tools >> Character Map, if you are on Win2K, or directly under Accessories if on WinNT. Not sure about where it is with other versions of Windows. Try to identify the characters you are seeing in the fields giving you trouble. See what their numeric codes are and check them against an ASCII table.
If you see that they are using control characters (which aren't being filtered 'cause Access is playing dumb), you've identified the problem as a stupid-user trick. Try educating your user group to not hit CR or LF when they really meant to use TAB. Hmmm.... ya know, Access really ain't PLAYING dumb ... it IS dumb. But I digress.
If it isn't misuse of control characters, then I'm not sure where to go next without knowing more symptoms and I'm not sure what to ask to help you find those other symptoms, if any.
Last time I saw anything like that, it wasn't an error. It happened because the form's behavior was set wrong with respect to use of tabs to move from field to field and whether it could cross records when you reached the last field. Also, you might wish to check if someone is trying to be "cute" (or lazy)by typing carriage control characters (CR, LF) rather than using tab characters, 'cause that is what it sounds like you are getting. Particularly if it is in a memo field, but a big text field could be just as bad.
Just a suggestion for sh|ts and giggles, but find out what font you are using in the data entry fields. Find it in form design mode, it is in the text-box's format property list. Then call up that exact font name using the Character Map tool. Use Start >> Accessories >> System Tools >> Character Map, if you are on Win2K, or directly under Accessories if on WinNT. Not sure about where it is with other versions of Windows. Try to identify the characters you are seeing in the fields giving you trouble. See what their numeric codes are and check them against an ASCII table.
If you see that they are using control characters (which aren't being filtered 'cause Access is playing dumb), you've identified the problem as a stupid-user trick. Try educating your user group to not hit CR or LF when they really meant to use TAB. Hmmm.... ya know, Access really ain't PLAYING dumb ... it IS dumb. But I digress.
If it isn't misuse of control characters, then I'm not sure where to go next without knowing more symptoms and I'm not sure what to ask to help you find those other symptoms, if any.
Once the record is currupt as you describe, the data is gone. Sometimes you can get lucky and salvage certain fields and only loose data in a few fields, and other times you can loose the whole record. There are some companies that say they can restore currupt data, but I am sure they are very expensive.
You should focus your efforts on why the records are being currupted. This could be a from a whole host of reasons. Data curruptions usually occur when someone is editing the record and something happens (like an abrupt network disconnect, ETC..).
I would check :
1. Network Integrity (i.e. wiring, NIC cards, and power). If you are having this problem on specific PC's it could be a wiring or NIC card issue. We have frequently had issues with specific DEll PC's with faulty integrated NIC cards, once we replaced the NIC card the users problems stopped.
2. We use small UPS units on all network PC's to protect against power dips and losses, which will reak havock on a access network setup and cause curruptions.
3. Access craves RAM, especialy in a large database. Be sure each workstation has plenty of RAM (min 256, optimally 512). Low ram can cause screen freezes and record curruption, if the user was editing when he has the loss of Ram. With access, the more RAM the better !
3. Make sure security (system.mdw file) has a unique user and password for each user, and that same system.mdw file is deplayed on the server and all workstations. Also check that your windows users & permissions are set up properly on the Back-End Server. You can get more locking conflicts if everyone signs in as Admin user.
4. Implement a good error handler that can record the errors, user, and form that an error occured on. This may give you a sense of where the trouble is starting.
5. Make sure the 'On Resume, Display Windows Screen" checkbox in the screen saver settings is unchecked. This will disconnect them from the network and kill their session with the backend database.
6. I am assuming you have split your database into a backend file and multiple front-end on each workstation, if you haven't you really should in a multi-user environment.
7. Sit with users having the problems, many times its a user issue in how they navigate your application which causes the problems.
8. It could be some event sequence in your programming that causes problems.
Anyway, tracking what causes curruptions is never easy, Starting with network integrity, RAM, and stable Power would be a good first check.
Good Luck
Jackson
You should focus your efforts on why the records are being currupted. This could be a from a whole host of reasons. Data curruptions usually occur when someone is editing the record and something happens (like an abrupt network disconnect, ETC..).
I would check :
1. Network Integrity (i.e. wiring, NIC cards, and power). If you are having this problem on specific PC's it could be a wiring or NIC card issue. We have frequently had issues with specific DEll PC's with faulty integrated NIC cards, once we replaced the NIC card the users problems stopped.
2. We use small UPS units on all network PC's to protect against power dips and losses, which will reak havock on a access network setup and cause curruptions.
3. Access craves RAM, especialy in a large database. Be sure each workstation has plenty of RAM (min 256, optimally 512). Low ram can cause screen freezes and record curruption, if the user was editing when he has the loss of Ram. With access, the more RAM the better !
3. Make sure security (system.mdw file) has a unique user and password for each user, and that same system.mdw file is deplayed on the server and all workstations. Also check that your windows users & permissions are set up properly on the Back-End Server. You can get more locking conflicts if everyone signs in as Admin user.
4. Implement a good error handler that can record the errors, user, and form that an error occured on. This may give you a sense of where the trouble is starting.
5. Make sure the 'On Resume, Display Windows Screen" checkbox in the screen saver settings is unchecked. This will disconnect them from the network and kill their session with the backend database.
6. I am assuming you have split your database into a backend file and multiple front-end on each workstation, if you haven't you really should in a multi-user environment.
7. Sit with users having the problems, many times its a user issue in how they navigate your application which causes the problems.
8. It could be some event sequence in your programming that causes problems.
Anyway, tracking what causes curruptions is never easy, Starting with network integrity, RAM, and stable Power would be a good first check.
Good Luck
Jackson
What are carriage control characters? I am pretty sure it has something to do with the user. Would it cause a problem if one user is looking at the record while another is entering data for that sample? All of these are excellent suggestions and I will work through them probably starting tomorrow. Any other suggestions from anyone are appreciated.
Gerald,
The actual field result you described sounds like a curruption in data, and I would be really supprised if it were just keystroke mistakes by users. I have seen the exact result in currupted records on hundreds of occasions. Curruptions generally happen while someone is editing a record. the blocks, dashes, pound signs, are signs of currupted data. You are actually lucky you are able to delete the record, I have had instances where the curruption is so bad I am unable to delete the record, and need to set up a whole new database.
I would seriously be looking at you network integrity, power supply and conductivity.
Good Luck !
The actual field result you described sounds like a curruption in data, and I would be really supprised if it were just keystroke mistakes by users. I have seen the exact result in currupted records on hundreds of occasions. Curruptions generally happen while someone is editing a record. the blocks, dashes, pound signs, are signs of currupted data. You are actually lucky you are able to delete the record, I have had instances where the curruption is so bad I am unable to delete the record, and need to set up a whole new database.
I would seriously be looking at you network integrity, power supply and conductivity.
Good Luck !
Gerald,
Sorry i didn't really answer your question, But it really should not be a problem for two people to concurrently view the same record.
- If you use optimistic record locking (No locks) with record level locking, both users can view a record, the first user can edit and save, If the second user tries to make a change to the record, he/she will be prompted to dischard his/her changes, discard the first users changes, or copy their's to the clipboard and view the changes made by the first user.
- If you use pessimistic locking (edited record locking) with record level locking the second user is not even allowed to open a record if the first user is editing the same record. If you want to rule out locking conflicts, switch to "edited record" locking with record-level locking, but this might not be great for the end-users who will not be allowed to open screens being used by another user.
So generally, access can handle multiple people viewing the same records. However, I have had issues where sites have not set up a security file properly and everyone is actually signing in as default admin user, in that instance we have seen some problems occur in this regard. So I do think it is important that security is setup giving each user a unique user name to avoid any locking conflicts.
Sorry i didn't really answer your question, But it really should not be a problem for two people to concurrently view the same record.
- If you use optimistic record locking (No locks) with record level locking, both users can view a record, the first user can edit and save, If the second user tries to make a change to the record, he/she will be prompted to dischard his/her changes, discard the first users changes, or copy their's to the clipboard and view the changes made by the first user.
- If you use pessimistic locking (edited record locking) with record level locking the second user is not even allowed to open a record if the first user is editing the same record. If you want to rule out locking conflicts, switch to "edited record" locking with record-level locking, but this might not be great for the end-users who will not be allowed to open screens being used by another user.
So generally, access can handle multiple people viewing the same records. However, I have had issues where sites have not set up a security file properly and everyone is actually signing in as default admin user, in that instance we have seen some problems occur in this regard. So I do think it is important that security is setup giving each user a unique user name to avoid any locking conflicts.
