Issue with Search Key Not Found Error (1 Viewer)

[nrgins]

So, occasionally we get the "search key not found in any record" error (error 3709). This usually happens when a particular field in a form is updated, and the record is attempted to be saved.

The assumption was that there was a corruption in the back end. So we do a Compact and Repair on the back end, and that clears up the problem. However, today I noticed something strange.

I went into the back end table that had this issue, and edited the field that usually causes the error and then tried saving the record. As expected, I got the error.

However, I then edited a different field in the same table record and was able save the record! So the corruption apparently was limited to that one field, which doesn't make a lot of sense to me, because I thought the entire data page would be corrupted.

As noted, this was done in the back end table, so there was no VBA code coming into play. There was no data macro either. Just a straight field being edited, nothing more.

I checked the table that it was related to, and in particular the record in the related table that it currently was using, and that table was fine. Was able to edit and save it.

The field in question is a Long Integer field, Required = Yes, Indexed = Yes (Duplicates OK).

It's related to a lookup table on the lookup table's PK field, which is an autonumber. Enforce Referential Integrity is enabled. Cascade Update and Cascade Delete are disabled.

Any thoughts as to why this error would be happening only with this particular field in the table record, and no other fields in the same record?

Version = Access 365.

Thanks!

 

[The_Doc_Man]

Create a new, empty database. From that database, go into the ribbon to find the tool that lets you get external data. You will, from the new DB file, import the contents of the old DB file. It is extremely important that you do the IMPORT to the NEW file because it is possible to EXPORT from the OLD file and they don't behave exactly alike. "Import to new" usually works more reliably than "Export from old." Don't ask me why, but many of us on the forum have, over the years, reported more success with the import operation.

Doing this will usually clear up index errors since part of this process includes re-writing the indexes. Since you are doing an IMPORT, the old file is not opened in the normal sense and therefore should not be affected badly by this process. Even so, if you were a nervous type, make a copy of the old file to some other "safe" folder just in case something goes willy-nilly on you.

 

[nrgins]

Thanks for that. I'll give that a try.

I'll note, though, that this is an intermittent problem. Over the past years, we've been getting it once in a while. Then we went about 8 months without an occurrence. Then, last month, it started happening maybe once or twice a week.

So this is just occasional, in case that makes a difference.

Also, do you have any insight into how a single field could be corrupted, but not the entire record or page?

 

[The_Doc_Man]

Without knowledge of usage habits for your users and without an appreciation of the stability of your network? No clue.

 

[nrgins]

Well, actually, I think what you said about it being a corrupted index would explain why it's a single field that has problems, instead of the entire record. Since each indexed field has its own index, then that would explain why a single field has the issue, if it is only that field's index that is corrupted, rather than the record itself that's corrupted.

 

[ebs17]

Indexed = Yes (Duplicates OK)

In an Access table, a foreign key field is automatically indexed when referential integrity is set in the relationship. You don't see this index through the wizards (property sheet, index window), but you can verify its existence through code.
A self-assigned index would be too much and rather harmful.

 

[nrgins]

In an Access table, a foreign key field is automatically indexed when referential integrity is set in the relationship. You don't see this index through the wizards (property sheet, index window), but you can verify its existence through code.
A self-assigned index would be too much and rather harmful.

This isn't a self-assigned index. Access assigns indexes automatically based on the values under Options | Object Designers | Autoindex on import/Create, which as by default set to: "ID;key;code;num."

Thus, any field that contains one of those text strings will be assigned an index by Access.

Are you saying that's wrong, that I should go in and delete all the indexes that Access automatically created based on the field names?

 

[ebs17]

Thus, any field that contains one of those text strings will be assigned an index by Access.

This automatic is always switched off for me because it often leads to fields being indexed multiple times.
I assign my indexes myself according to plan and check the total inventory in the index window.

Multiple indexes on the same field regularly make no sense. They do not generate any additional benefit, but they have to be managed and cost resources, including runtime in the case of writing actions.

It would be more specific to look at when a field is included in a composite index and at the same time has a single index.

 

[Gasman]

Crossposted https://www.utteraccess.com/topics/2065025?post=unread

 

[The_Doc_Man]

This isn't a self-assigned index. Access assigns indexes automatically based on the values under Options | Object Designers | Autoindex on import/Create, which as by default set to: "ID;key;code;num."

Thus, any field that contains one of those text strings will be assigned an index by Access.

Are you saying that's wrong, that I should go in and delete all the indexes that Access automatically created based on the field names?

On small tables with less than 10 fields AND you don't manually add an index, it probably is not harmful. ("Not harmful" does not equate to "desirable".) But on wide tables there is a limit on the number of indexes a single table can have. Automatic indexes would consume some of that quota and limit your choices for manual indexes.

ALSO, if the table is both wide AND deep, the indexes can be quite big and an UPDATE query that includes an indexed or any bulk INSERT INTO query suddenly becomes a processing burden because indexes would each update once per indexed field per record, with potential index re-optimization at unpredictable times. In this case, too many automatic indexes would be a serious time killer.