If this is not corruption, what else can it be??? 1

[BoulderRidge]

One form in my Access application is behaving badly. It normally opens in Data Entry mode to a new blank record, but today it is opening on the first record in the table with all 5900 records available to view. I have a drop-down that filters these records to Last Day, Last 7 Days or Last 30 Days and these filters which normally work are today returning random results.

This is a multi-user app with each user running a separate copy of the front end app database which is linked to a shared back end database containing the data tables. The app was developed in Access 2007/Windows Vista and has been deployed in an Access 2010/Windows 7 environment. (I know this is not ideal but it has been working for 18 months.)

I suspect corruption: midday today a user had her tablet crash badly, losing network connectivity while she was in the front end app. Since then nobody else can save any edits to a record or create a new record.

The primary key on the three main data tables was gone, which I have seen before as an effect of corruption. During the time this key was missing, one record with a duplicate key was created. Now both records having this duplicate key have been deleted, the primary key on all main tables has been restored, and the database was repaired and compacted. This did not fix the problem, so we created a new empty backend database and imported all the tables into it, refreshed the links from the front end app and tried the tasks again. Still cannot add new records.

We also replaced the front end copy of the app with a clean backup, refresh the links to the new clean backend database, and tried the tasks one more time to no avail.

If this is corruption, it must be in the shared backend database but this only contains tables—no forms or code—so how can I get rid of any remaining corruption without losing the data AND without resetting the autonumber values that are foreign keys to other tables?

If this is not corruption, what else can it be??? Any tips would be greatly appreciated. Thanks!


-- BoulderRidge B-)

 

[BoulderRidge]

It was corruption but a stronger flavor than I have seen before--one of the indexes on one of the main tables was corrupted. I became suspicious when I saw a post mentioning random results from a filter being related to a corrupted index. So I deleted ALL the indexes on each of these main tables, saved the change, repaired and compacted, and then recreated the indexes, saved, and did another repair and compact (probably not needed but getting a little paranoid).

Closed the backend database and relaunched the front end app, went to the user screen that should open in data entry mode, and it DID! All the filter options were also working correctly now.

Hope this post might help someone else resolve corruption after the usual techniques are exhausted.

-- BoulderRidge B-)

 

[lameid]

That's a new one to me. Have a star.