corrupted text in records affects 'index'(?)

[hammerman]

Hi. I have an Access.mdb database (2007?) that tracks our test equipment. Occasionally, when modifying records, (updating calibration dates, especially, or adding new records via a form designed by program designer), one record becomes corrupted. The dB has essentially 2 tables; one tracks calibration specific data, (date done, date due next, who did it, etc), the other is the item specific details, (what it is, who made it, who sold it, its mfgr s/n; our asset tracker bar code #, how it was purchased, when, how much $, and who is using it, and a few other details.) they are linked into a query by a K/FK. All transactions, (edits or updates), on the records are done via queries; this program is 5 yrs old or more. I inherited it.
The issue only appears in the larger table, the instrument details table. (almost) all fields text changes to far-eastern glyphs. I can't delete the record, I can delete some field text, but I get the error when I move off that record, "the search key was not found in any record".


The operation of the 'general user' doesn't seem to be impaired, those queries that allow users to change 'ownership' work. but the query I use to manage all the data, which combines info from both tables into a datasheet view doesn't even load. (error -2415????). Also, both queries to view the records, (one = read only, the other =editable), do not sort the datasheet view as they did before the corruption occurred.
My questions: what causes this?
How do I recover from this?

[orange]

By searching the forum I found posts that indicate the "chinese characters/gibberish" seems to be corruption related. I did find 1 post https://www.accessforums.net/showthr...4574#post14574
that tracked the cause to a "Group By on a memo/longtext field". I don't know if this applies in your case.

I did see that compact and repair did not seem to repair this, but it is certainly worth trying. I would recommend making some backups before experimenting with possible solutions.

You can search the forum or internet generally and see if you find something that is more related to your situation.

[isladogs]

There could be many reasons but any corruption observed should be dealt with as a priority as otherwise your database could become so badly corrupted that it is unusable.

First of all make a backup or two
As you are using Access 2013 so you should have updated to an ACCDB database many years ago.
This is a better file format in many different ways. Strongly recommend you convert after doing everything else I'm about to list

From your description, this is a multi-user database.
Is it a split database? If not then you need to make it so ASAP
If it is split, does each user have their own copy of the front end on their own computer (with a shared backend database on the server?)
If more than one user is SHARING the same copy of the database it WILL become badly corrupted.

However, if it is corrupted already, you need to fix that ... THEN split & distribute as described above.

Make a BACKUP - I know I've already said that ... do it AGAIN

Now work on one of the backup copies you've made
If it goes wrong at any stage, bin that & go to work on another copy - keep at least one copy spare just in case

Check that the project requires variable declaration in the VBE Options
If not, add that
Next check that EACH code module has these 2 lines in the Declarations section at the top of the module

Option Compare Database
Option Explicit

If either of those are missing, add them in each code module

Next see if the project COMPILES in the VBE
If not, work through and fix all compile errors until it does compile

Next DECOMPILE the database - see http://www.fmsinc.com/microsoftacces.../decompile.asp
This will remove any corrupt compile code that you may have.
Now RECOMPILE the database
Make ANOTHER backup

Now look at the suspect tables, note which are corrupt then delete all records with corrupt data.
If you have an old backup you can try to restore these records later if the old backup isn't corrupt

At this point, if you feel confident looking at the hidden system tables, you could attempt to repair the MSysObjects table
Follow the suggestions in this thread: https://www.access-programmers.co.uk...d.php?t=293579

Next run compact & repair (C&R)
If you have C&R set to run automatically when the database is closed, turn that off as it can also lead to corruption.

If ALL of that failed, you can try creating a new blank database & copying all database objects into that.
However, unless you have already done the earlier points, that will either fail for certain objects (LIKELY) or the corruption MAY be transferred (UNLIKELY)

After all of that, if you now have a working copy, setup a schedule for creating backups - at least once a day in most situations

Good luck

[hammerman]

By searching the forum I found posts that indicate the "chinese characters/gibberish" seems to be corruption related. I did find 1 post https://www.accessforums.net/showthr...4574#post14574
that tracked the cause to a "Group By on a memo/longtext field". I don't know if this applies in your case.

I did see that compact and repair did not seem to repair this, but it is certainly worth trying. I would recommend making some backups before experimenting with possible solutions.

You can search the forum or internet generally and see if you find something that is more related to your situation.

Thanks, orange! I don't believe the 'group-by' scenario applies. the only criteria is sort by the bar code asset number, found in the larger table. And yes, compact and repair does not work.
Thanks for the referrals, I will look around. (with all the wisdom and patience exhibited by those who have answered all the questions raised in this forum, I knew someone had seen this before. So this was my first resort.

You are all appreciated, for your knowledge and patience in answering all the questions.
thanks!

[hammerman]

Thanks, ridders52
I've followed some of your suggestions; our group has plans to replace this dB with a professionally marketed asset management software package which will be shared throughout our corporation.
I did manage to do a conversion, but had errors. After which, I was able to delete the corrupt record. Since this asset is being scrapped, I can replicate the pertinent data into a separate table and Excel sheet.

Thanks everyone for all the help!

[orange]

For anyone with an issue of "chinese characters/gibberish", I found this info from theDBGuy which I hope is helpful.
It seems there may be more than one cause for this corruption.

Good luck.

[hammerman]

Thank you orange, again. None of the scenarios expressed by theDBGuy apply in my case. But thanks for being proactive for others who have seen this problem.
Good day!