?
Solved

ACcess 2003 Corrupted data (nothing helps)

Posted on 2013-10-24
12
Medium Priority
?
262 Views
Last Modified: 2013-10-24
My Access db has legacy tables from my client, but one of them has corrupted data. I've tried everything I can think of: (1) Exported into a clean db, (2) Used query to make a  table from the original one. Nada.

I have one combo box filtered by the value in another. But the filtered one does not recognize the values in the 1st box. Instead of using the Forms![frm]![object] to refer to the first box , I even put a number in the filter expression of the 2nd box. Still doesn't recognize the value.

I'm embarrassed to admit how many hours went down the drain before I realized it was the data.
0
Comment
Question by:ms3930
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39596680
Do the values in the 'corrupted' table look like normal alphanumeric values or do they look odd, like hieroglyphics (indicating corruption)?


If the values look normal, it could be that the data contains leading/trailing spaces or invisible control characters such as line feeds, etc that are preventing your criteria/comparisons from matching up where you would expect.  If this is the case, you can run an update query to clean up the data once you identify what characters need to be removed.

Can you post a sample database containing just the relevant tables wirh some of this data and the form/combo boxes you are working with?  Mask or remove any sensitive data before uploading the sample.
0
 
LVL 58
ID: 39596875
1. Make a copy of the DB.

2. On the copy, remove all indexes on the problem table.  Also remove all relationships.

3. Create a fresh DB.

4. Import the tables, with structure and data, expect for the table in question.

5. Import the problem table, but with *structure only*

6. Link to the problem table in the new DB to the old corrupted DB.

7. Create an append query to move the records in chunks.

  You may have to repeat this several times to narrow things down to the record(s) that are causing problems.

With all that said, on this:

<< I even put a number in the filter expression of the 2nd box. Still doesn't recognize the value.>>

 Does the rowsource the combo use display data correctly on it's own? Is it set to the table directly or does it use a query?

Jim.
0
 
LVL 1

Author Comment

by:ms3930
ID: 39597908
Just an update on your comments:

1. The data looks normal. No gibberish.
2. The rowsource uses a SQL statement within the control (no separate query)
3. If there are bad characters in the data, it's tough to identify them

I am am now trying the procedure suggested by MDettman.

I have also attached a file. When you open the form, and select an office, the "Agents" box populates correctly. When you do it a second time, the data does not change. Strange, huh?
MS3930-UploadFile.zip
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39598025
It might be that the form is corrupted, not the data.  Try making a brand-new form and see if the combo box works correctly.
0
 
LVL 1

Author Comment

by:ms3930
ID: 39598155
Not the form. Created several.
0
 
LVL 58
ID: 39598334
Your attached file link in the above doesn't work for some reason.  Please upload it again.

Thanks,
Jim.
0
 
LVL 1

Author Comment

by:ms3930
ID: 39598523
Here's the file.
MS3930-UploadFile.zip
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39598637
Your data looks fine.

You just need to requery your Agent combo box to refresh the list after selecting an office:

Private Sub cboOffice_AfterUpdate()
cboAgent.Requery
End Sub

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 1500 total points
ID: 39598654
It looks like you tried to do that, and were actually very close... but you put the requery statement in the wrong event.  It was in the AfterUpdate event of the Agent combo, but should have been in the after update event of the Office combo.
0
 
LVL 1

Author Comment

by:ms3930
ID: 39598684
I thought I had put it in the Afterupdate event of he office box. Anyway, when you all made that change, did it work correctly? Remember, it works when you make the first selection, but not after that.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39598854
Did you try making that change yourself?  Note that you cant simply change the name of the sub in the code - it won't automatically connect to the right event procedure.  You need to make sure the words "Event Procedure" are showing in the box next to After Update in the property sheet.  Clicking the "..." and choosing Code Builder automatically makes that connection for you; editing an existing event procedure does not.

< Anyway, when you all made that change, did it work correctly?>
Anyhow it does appear to be working correctly.  Here's the sample with that change in it:
MS3930-UploadFile.mdb
0
 
LVL 1

Author Comment

by:ms3930
ID: 39599555
The solution was actually in two steps:

1. The requery was in the wrong spot (an amateurish error)

2. The data was tainted. So I migrated it into clean tables, which I recreated manually, and then used DAO & recordsets to save it in the new ones.

Thank you for your timely help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question