[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Data unexpectedly transformed into Chinese characters

Posted on 2007-07-25
10
Medium Priority
?
551 Views
Last Modified: 2010-05-18
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?
0
Comment
Question by:Alastair_White
  • 6
  • 3
10 Comments
 
LVL 85
ID: 19567477
First: I'd immediately do away with Cascading Deletes as it's a sure way to completely destroy your data ... Cascading Updates is okay, but in a properly designed system you shouldn't have any need for that. If you need to delete data, then do it yourself with VBA or SQL ...

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).
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 19567841
Possible corruption >>>

A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

0) **Backup your MDB**
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

6) Close the mdb
7) Compact and Repair one more time.

*** Executing the DeCompile:
Here is an example of the command line syntax  (adjust your path and file name accordingly) for executing the

decompile:

Run this from Start>>Run, enter the following command line ...

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile

"C:\Access2003Clients\AzDoc\Pgrm\AzDocPgrm2K3.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 

Author Comment

by:Alastair_White
ID: 19568485
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?


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 19568573
"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
0
 
LVL 75
ID: 19568579
Try the decompile process above ... so far this week .... 4/5 success rate :-)

mx
0
 

Author Comment

by:Alastair_White
ID: 19569259
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.
0
 
LVL 75
ID: 19569293
I did check out your comment.

No ... try the decompile NOW, LOL !

mx
0
 
LVL 75
ID: 19569314
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
0
 

Author Comment

by:Alastair_White
ID: 19573970
OK, MX

Did the backup/decompile/recompile/backup again/create new/import all/tweak a bit/save/save again blah blah blah dance for a couple hours yesterday.

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?
0
 
LVL 75
ID: 19575688
That makes 7.5/8 for decompile this week ... could be new record !

great.

mx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

834 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