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.
Liberty4allAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I would start by making a backup, then doing a Compact & Repair.

mx
0
Liberty4allAuthor Commented:
I did but it did not fix anything.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Liberty4allAuthor Commented:
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.
0
Liberty4allAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Welllll ... I don't recall C&R causing this sort of issue, but I suppose anything is possible .  
I have a semi automated backup routine that among other things, does a C&R on 25+ dbs every night ... and I have never seen this effect .... just sayin' ....

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Liberty4allAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.