Data corruption - chinese characters appearing. 6

[AKMonkeyboy]

Occasionaly I will get boxes and chinese characters in all but the Primary index field of a table. A user tells me that it appears after they return to a record that they have just recently edited. I have personally never seen the corruption appear, but as I "administer" the database I find it when I run maintenance on the table.

I cannot delete the corrupt records, when I try I get a message "The index could not be searched" or something to that effect. I end up removing the records using queries.

Any ideas as to what could be causing this corruption? Sorry for the lack of detail - perhaps I can find out more from my user.

Adam

 

[mok44]

DId anyone find a resolution to this strange occurence?
Thanks

 

[Databaseguy]

My experience has been that a field or records will get corrupted during abnormal termination. That is, power outage, user hitting ctl+alt+del, shutting of the machine, etv.

 

[AKMonkeyboy]

I found the problem. I'll explain what happened so you can avoid this issue. I'm surprised more people haven't seen it.

1)User opens form:
FrmCustomerData
Data Source = TblCustomers

This form displays customer information. User wants to add contact for customer currently being viewed. The user would click on a button that would open a form used to add customer contact information

2)Secondary form would open:
FrmCustomerContact
Datasource = TblContact

A hidden field on this form would record the customer key from FrmCustomerData that was (and still is at this point) open. User could then record contact information and info would be saved with relation to current customer (this data is stored in a separate table from the customer information). User would then push a button that would run an append query saving entered data to TblContact.

Now - here's the caveat. This form would also attempt to perform an edit using an update query to TblCustomers (I was updating the last contact date). The problem was, the same record was ALREADY open and in a state of being edited (even if no changes had been entered) on FrmCustomerData.

Solution: When user clicks button to add customer contact perform the following:
Open FrmCustomerContact
Retrieve key data for customer record in FrmCustomerData
Close FrmCustomerData

This would close the recordset on TblCustomers. At this point, the button on FrmCustomerContact was free to perform BOTH queries (the append and the edit TblCustomers) without causing conflicts. I read somewhere on this forum that one of the most important things you can learn while writing code is CLOSE UN-NEEDED RECORDSETS!! I guess I learned this the hard way.

I hope this helps - it's been so long since I fixed this that I may have forgetten some of the specifics. If you are still confused, let me know and I'll see what I can do to help clear it up. Sure was a hassle for me. I tried everything.

 

[mok44]

This occured again on my app. My design is similar with a main form and tabs to open subforms. For example the main form contains name and prim key, subform contain addresses and linked to prim key of main form. Users indicate this happened while they were updating address information. How do I apply your fix?
Thanks.

 

[AKMonkeyboy]

I suppose it depends on the depth of your tables. Do you have one table open on both the main form and the subforms?

Any time you try and update a recordset that is already open you run the risk of corruption. For example, if your main form is based on "TblData" and you have a sub-form open based on the same "TblData" you have one recordset open twice, so Access won't know which changes to save and which to drop.

Does this explain your database? If so, you're going to have to:

1) Lock the data displayed on the "main" portion of your form. Allow changes only on the subform portion. (I think that would do it.)

or

2) Remove your subforms and go to a more complex "Open new form" when you want to edit your addresses, in other words; place a button that opens another "filtered" form to edit your addresses, making sure to close the "parent" form when the "child" form opens.

If I've confused you, let me know. It's kind of hard to help not knowing what exactly you're getting into.

Glad to be of service - this site has saved me countless hours of frustration.

Monkey

 

[mok44]

My app is design main form and tabs to open subforms; you're right it seems the error occurs only when I update this subform based on the same table as the main form. After updating the subform, I am opening another form (filtered) for comments (also based on same table); when I exit this last form and open another subform (this time based on another table) I get first a Write Conflict message then sometimes the boxes and chinese char.
Sorry this is so conveluted...
Thanks for helping.

 

[AKMonkeyboy]

Yep, I would consider moving your comments to another table - probably would do the same with your addresses. This would give you the added benefit of "unlimited" addresses and comments for your main record. That would be the easiest thing to do (unless you have 10,000 records that you would need to "move&quot.

Otherwise you'll have to rachet-up your data integrity rules. Close record sets before allowing edits, etc.

Didn't see any specific "questions" in your last post, so I hope you've got it figured out. If not, let me know and I'll do what I can to help.

 

[lunarmono]

Hey All,

I found an easy way to clean these out of tables and prevent them from causing errors in action queries. It's very simple, just cut and then repaste the tables that contain these entries. The corrupted records do not get copied for the same reason that they cannot be deleted.

 

[MarrowGenx]

I have run across this problem a number of times and the way I get around it is with queries. The lines that appear as chinese characters I either put into queries or alter the existing queries to pick those field "First Of". Dont ask me why, but this works. Anytime I see chinese characters appear, i just put a "First Of" and it disappers

 

[mok44]

HI MarrowGenx
But are you deleting valid records that got corrupted? Or do your records recover with the values that got replaced by the Chinese char?
Please explain.
Thanks

 

[AKMonkeyboy]

I have used queries before to remove corrupted records. I keep regular backups of my databases and import the un-corrupted record from backup copies. To my knowledge there is no way to restore the Chinese records once they become corrupted.

 

[mok44]

It's been a while since I visited this thread...The best thing of course is to prevent the corruption from occuring. In my case, edits were not yet committed when the user was moving to a new record. I just added a save command before executing the open command of the subform. Did the same for my search buttons (cmboxes) on the main form. Seems to work fine, have not had one occurence of this bug yet.
If anyone needs more detail, let me know.

 

[PeeweeNo1]

Hello

I've got the same problem in my database - one of my records in the company table has become corrupt and is showing these chinese characters. I've tried copying the table and pasting it to get rid of the record but everytime I try to do this, access closes down and says an error has occured. What do you mean by you get rid of it using queries? How exactely do I do this?

Many thanks
Leigh

 

[AKMonkeyboy]

Have you looked above at lunarmono's post? I haven't tested it, but it would certainly be the easiest way. Otherwise here's what I do:
1) Right click on the "corrupt" table. And choose "Copy"
2) Then, go to edit, and choose paste. IMPORTANT - when Access asks you what you want to do, choose "Structure Only". I usually use the same name for the table with the post-fix of "good" (i.e. TblMyDataGood)
3) Assuming you've used autonumber (or another sequential index) you'll need to find the record # (or #'s) of the corrupt line. Typically you can't read ANYTHING except the Index field. If you can't read the index, you should be able to tell where in the sequence the corruption occurs. For example, the line above you corrupt record is 1245, while the record below it is 1247, thus your corrupt record must've be 1246.
4) Create an append query. The source table will be your table with the corrupt data. The output table will be the table you pasted in step 2. Be sure to include ALL FIELDS in your append, or you won't get all of your information.
5) To filter out the corrupt records, add to the criteria of your index field (the autonumber field talked about in step 3) to NOT include the corrupt record. The process may take several &quot;appends&quot;, depending on how many corrupt records you have. For example, assuming your corrupt record has been determined to be record #1246. The criteria for the index field would be <=1245 and >= 1247. You can't say <>1246 because that record is corrupt and to Access, doesn't exist. You'll get errors if you attempt to use it as criteria. Another problem would arise if you have multiple corrupt records - Let's say #1246 and #1569 are corrupt. You would run the query THREE times to get rid of them. Each time changing your criteria - something like this:

1st run criteria: <=1245
2nd run criteria: >=1247 AND <=1568
3rd run criteria: >=1570

The end result here is that you will have 3 fewer records in your &quot;TblMyDataGood&quot; than you do in your corrupt table.

6) Delete your &quot;TblMyData&quot; and rename &quot;TblMyDataGood&quot; to &quot;TblMyData&quot;

Make sense? I hope so. Let me know if I've left out a step and you are lost. I'm doing this from memory and what may be easy for me might not make sense to you (especially the way my memory works;-) )

Let me know if you need more assistance.

Adam

 

[PeeweeNo1]

Cheers for your reply - Yes I did try this as well (in a round about way) - however (using your example) won't this mean when all the records from 1247 onwards are posted into the new table - their ID will change (it is created by autonumber) as record 1246 no longer exists - so in the new table 1247 becomes 1246. This ID number is used on all the paper files in my office, so I wouldn't really want to change it. Is there no way of deleting this corrupt record without creating a new table and copying all the data over?

I suppose if there is no way around this I can always allocate the current ID number to another field in the new table and just use the autonumber field as a unique identifier that is not printed out on any files.

Cheers
Leigh

 

[AKMonkeyboy]

No - if you post your current ID number to the ID field in the new table the same numbers will be carried over. You will, however, be missing the corrupt record, so you could have some orphaned records in related tables.

As I mentioned in an earlier reply, I like to keep frequent backups so I can usually &quot;go get&quot; the corrupt record from a backup - old data is better than no data.

This post is quite old and I haven't heard any way to get rid of the corruption directly in the &quot;bad&quot; table.

Make a backup of your database and try my solution, you'll see that the ID numbers remain the same. Of course the best way to get rid of corruption is to not let it happen in the first place. I'm hoping you know the source of the problem and have taken steps to prevent it (read this thread in detail if you don't know what I'm talking about).

Have a spiffy day.

A

 

[PeeweeNo1]

Hi Thanks - that worked. I've sorted it out now as well - only 1 record was corrupt and I've changed it so that it won't happen again.

Have a star!

Cheers
Leigh

 

[b31luv]

I had corruptness, but I found my answers here. Thank You Tek Tips and everyone that provides post.

 

[mok44]

I was getting records corrupted when updating data between form and subforms. In my case I added a save command before closing a subform. Has anyone found other causes to this problem? Please share so everyone can take preventive measures.
Thank you all.