Data in Access table not displaying correctly
I have an Access table with many records and fields. Something occurred in the last few days I have never seen before. When opening the table in Datasheet View it appears that some values in Field1 were placed in Field2 and were then either deleted from Field1, or the values in both fields were swapped. This is also what appears when looking at the records through a form bound to the same table. However, when using a select query to view these same records no such thing appears. The select query displays the records exactly as they should appear.
Since the select query displays the records as expected I'm not able to change the data in the table. Normally I would update values from Field2 to Field1 to fix this but the select query doesn't find any records that match what appears to be wrong in the table.
I have no clue what is causing this. No error messages occur and everything looks and works fine. The table is compacted weekly and, in fact, was compacted last night. Any suggestions to solve this would be greatly appreciated.
Since the select query displays the records as expected I'm not able to change the data in the table. Normally I would update values from Field2 to Field1 to fix this but the select query doesn't find any records that match what appears to be wrong in the table.
I have no clue what is causing this. No error messages occur and everything looks and works fine. The table is compacted weekly and, in fact, was compacted last night. Any suggestions to solve this would be greatly appreciated.
ASKER
I did but it did not fix anything.
What about creating a new, blank db and importing all objects into that db.
OR ... for starters, a new table with the same structure, and move those records to the new table.
mx
OR ... for starters, a new table with the same structure, and move those records to the new table.
mx
ASKER
If I create a new table and import the same records into it, won't that cause the same problem to be passed to the new table? I'm thinking that "corrupt" records in the existing table will also be "corrupt" in the new table and I have no way to filter them out before importing.
ASKER
Based on the sequence of events I'm guessing the problem was caused by compacting the file the day before. Prior to that there was no problem and no development occurred prior the issue occurring today. Fortunately I made a backup prior to compacting. That allowed me to replace "corrupt" records with those which are valid. I don't know if the problem was with the front or back end as both were compacted. I have experienced problems with compacting files before but not frequently. I'm told it is best to compact and repair often to avoid corruption, but there is a small chance that doing so can also cause corruption which is why making a backup copy is prudent.
Does this conclusion sound reasonable? If so, I will accept it.
Does this conclusion sound reasonable? If so, I will accept it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
While I cannot determine the source of the problem it appears it may be related to compacting and repairing the file. I have experienced problems with compacting before so I'm not totally surprised. The backup copy was not compacted and did not have problems, so I'm guessing the act of compacting caused the problem. User error is not suspected at this time.
mx