Data unexpectedly transformed into Chinese characters
Got to work this morning - one particular record in my Access 2003 back-end "Job Log" table, which is the central table around which my whole system is built is all of a sudden filled with Chinese characters.
Last night's backup was OK, so I tried replacing that record after compacting and repairing and deleting the record in the Job Log table - but the weird thing was that all the related records in other tables were still there. I would have thought that the Cascading Deletes in the Referential Integrety business would have caused all the related records to go away.
Anyway I replaced the record in the Job Log, but when a user tried to access that job, all the data turned ito nubmer signs (not Chinese this time). I compacted and repaired again, and deleted the record again, but the related records are still in the other tables. Scary. Help. Is my world about to fall apart?
I did make some changes to the table yesterday - I changed a text field to a yes/no, but only after making sure the values were all "yes" or "no". Could this be a catastrophic error? Should I go to all related tables and manually delete all data associated with this one particular job number, then append the data from the backup?
Last night's backup was OK, so I tried replacing that record after compacting and repairing and deleting the record in the Job Log table - but the weird thing was that all the related records in other tables were still there. I would have thought that the Cascading Deletes in the Referential Integrety business would have caused all the related records to go away.
Anyway I replaced the record in the Job Log, but when a user tried to access that job, all the data turned ito nubmer signs (not Chinese this time). I compacted and repaired again, and deleted the record again, but the related records are still in the other tables. Scary. Help. Is my world about to fall apart?
I did make some changes to the table yesterday - I changed a text field to a yes/no, but only after making sure the values were all "yes" or "no". Could this be a catastrophic error? Should I go to all related tables and manually delete all data associated with this one particular job number, then append the data from the backup?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Why are cascading deletes a bad thing? If I delete a Job Number, I want all the associated Notes, Drawings, and every other piece of data associated with that Job Number to go away. I thought cascading deletes was the way to do that.
I've been a functioning illiterate in the database thing for about 10 years now, doing my little thing with some Access databases that provide valuable, albeit limited, functionality to my employer. As time has gone on, I've tried to learn more about database design and creation within the MS Access environment, but I'm not a professional developer by any means.
So when you say "in a properly designed system", I'm afraid I don't know what that is, particularly in terms of the behavior of related/foreign data (on the "many" side) in response or reaction to actions taken on the main/primary data (on the "one" side). Any recommended reading about this topic?
Back to the question and your potential solution - if I start a new db and import the tables from my existing back end - yes, it is "split" - won't the problems come along with the imported tables? Or do I import the architecture only? Then copy and paste the data? How should I do that. Because I want to do it this evening after everyone leaves for the day.
Users do report "slow" to "very slow" opening of the forms in the various front ends. But there have been no outages, nor does anyone have slowness in their Internet connections.
Could the problem be in one or more of the forms in one or more of the front ends? Is it possible for a form to be "corrupt" and thereby corrupt the data? Or does corruption exist within the tables only?
I've been a functioning illiterate in the database thing for about 10 years now, doing my little thing with some Access databases that provide valuable, albeit limited, functionality to my employer. As time has gone on, I've tried to learn more about database design and creation within the MS Access environment, but I'm not a professional developer by any means.
So when you say "in a properly designed system", I'm afraid I don't know what that is, particularly in terms of the behavior of related/foreign data (on the "many" side) in response or reaction to actions taken on the main/primary data (on the "one" side). Any recommended reading about this topic?
Back to the question and your potential solution - if I start a new db and import the tables from my existing back end - yes, it is "split" - won't the problems come along with the imported tables? Or do I import the architecture only? Then copy and paste the data? How should I do that. Because I want to do it this evening after everyone leaves for the day.
Users do report "slow" to "very slow" opening of the forms in the various front ends. But there have been no outages, nor does anyone have slowness in their Internet connections.
Could the problem be in one or more of the forms in one or more of the front ends? Is it possible for a form to be "corrupt" and thereby corrupt the data? Or does corruption exist within the tables only?
"Why are cascading deletes a bad thing?"
They are actually a beautiful thing ... WHEN ... you have a 'correct' relational model AND the are applied appropriately.
mx
They are actually a beautiful thing ... WHEN ... you have a 'correct' relational model AND the are applied appropriately.
mx
Try the decompile process above ... so far this week .... 4/5 success rate :-)
mx
mx
ASKER
Will decompile after work today.
Man, turns out that incremental thing didn't really work like I thought it did. If you get a moment, check out my latest comment. Dang.
Man, turns out that incremental thing didn't really work like I thought it did. If you get a moment, check out my latest comment. Dang.
I did check out your comment.
No ... try the decompile NOW, LOL !
mx
No ... try the decompile NOW, LOL !
mx
Did Harfang have a code solution. And hey ... my original table idea WILL work ... why not use that. In fact, post a new Q ... and I will post that.
mx
mx
ASKER
OK, MX
Did the backup/decompile/recompile
So far, I've gotten everything out to a few users and they are happily reporting a non-recurrence of the terrifying events of yesterday.
No invasion of Chinese characters, no bizarre behavior, so far so good.
Turns out some of my relationships were also missing. That's nutty, since I haven't deleted or changed the design of the tables in question in like forever.
Whatever. I'm saying all good for now. We'll see what happens next. Is SQL Server less prone to this sort of thing?
Did the backup/decompile/recompile
So far, I've gotten everything out to a few users and they are happily reporting a non-recurrence of the terrifying events of yesterday.
No invasion of Chinese characters, no bizarre behavior, so far so good.
Turns out some of my relationships were also missing. That's nutty, since I haven't deleted or changed the design of the tables in question in like forever.
Whatever. I'm saying all good for now. We'll see what happens next. Is SQL Server less prone to this sort of thing?
That makes 7.5/8 for decompile this week ... could be new record !
great.
mx
great.
mx
Anytime you have data that is giving trouble you should be wary. The first thought is corruption, and to be on the safe side I'd immediately import my data to a new, blank database and begin using that db.
Since you mention "back end" I'm assuming you've split the database ...
One issue that can cause corruption is flaky network hardware ... make sure the users aren't reporting sproradic network outages, or "slow" forms (or even slow Internet connections).