Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    5

    Question Unanswered: Random Foreign Characters in Table

    Hello! I'm running access 2003 and have a table that multiple users are updating via forms. Every so often it appears that the data in the table gets corrupted and alters the data in the table to appear in what looks like a combo of chinese and even maybe some wingding font? I've heard that it may have something to do with users using the scroll wheel while in the form. Anybody else have this happen or have any ideas on what I can do to prevent it from happening?

    I appreciate your help!
    Last edited by wazup3d; 08-02-07 at 19:50. Reason: Typo

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've seen this with a corrupt mdb file and ran the compact and repair and even went to the extent of starting a new mdb and importing all the forms, tables, reports, etc... I've never seen it being produced by the scroll wheel but maybe others have some insight...wait a minute...it's coming back to me now..corrupt data...no autonumber field...

    Now I recall and I'm guessing you don't have an autonumber/identity field for that table (if not, you may want to create one.) I "think" this was possibly an issue once but I can't be certain. Actually, I do think this was the issue!! (it was years ago though) Regardless, I've always created an autonumber field for all my main data tables. Also, establish a primary key. I'm guessing you don't have an autonumber field on that table but let me know - I'm curious. This would make sense with the scroll wheel as the code for doing an acSave would not be fired which you really don't need to do with an autonumber field in the table. Again, let me know if you have an autonumber field.

    I "think" I also may have seen it with the mdb being accessed via a slow network with daisy chained hubs (or via Citrix).
    Last edited by pkstormy; 08-02-07 at 19:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    5
    Thanks for the info... we do have a primary key and an autonumber field but the primary key is not the autonumber field. It's one of the other fields in the table.

    Some days we get 1, others 4 or 5 and sometimes we get none.

    It's been a real brain burner!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just out of curiosity... What is the autonumber field?
    Can you describe each (autonumber & PK) and their purpose?
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Posts
    5
    I'm a newb to this whole thing but the autonumber is a field that automatically generates for each new record added to the table.
    It starts at 0 and increases by the value of 1 for every new record.

    The primary key (not required in all situations) is set on a field that contains a unique value for each of your records, in most cases it would probably be the autonumber, but in our case it's another field because we don't want duplicates of that field in our table. We don't use the autonumber for anything at this point but when we created the database we chose not to delete it.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Dang. I really thought it was the autonumber. Can you tell me if you use Citrix or if perhaps your network uses daisy chain hubs verses a switchbox? I'm trying to recall what it was that made the foreign characters appear. I know it was something and perhaps I'll remember in the next few days. I also wouldn't mind seeing this again if you could perhaps upload a zipped example with a few of the foreign character records (and delete the other records you have.) It might instigate a memory recall.
    Last edited by pkstormy; 08-02-07 at 21:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    wazup3d.
    If you are using an autonumber that should be your primary key.

    with regards to your other field you can actually index the field to not allow duplicates (open your table in design view, select the offending field and set "Index" to "Indexed (No Duplicates)".
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Guys, are we talking about the same thing, here?

    A long integer field, with a default value of either random or incremented numbers?

    If such field exists in a table, and is not used as Primary Key, it causes corruption?

    Or not having such field in a table, causes corruption?

    I don't think confusing corruption with the surrogate vs natural keys debate sheds any light here, the one "Auto"<thingie> known to cause corruption, is the "Name AutoCorrect" - some refer to it as "AutoCorrupt" .

    I think a read on corruption, for instance Tony Toews Corrupt Microsoft Access MDBs FAQ http://www.granite.ab.ca/access/corruptmdbs.htm or Allen Brownes section on corruption (search for Corruption from his main Access page) http://allenbrowne.com/tips.html, will show more likely suspects of causing corruption. Both sites have good listings about different causes, how to prevent corruption, and how to recover.

    Couple of quickies
    * turn off Name Autocorrect (Tools | Options | General tab)
    * use a split setup, where each user has their own copy of the front end on their computer (not doing this, is in my view inviting corruption)
    * ensure you're on the latest SPs (Service Pack) on Jet/Office/MDAC
    (and, as pkstormy suggests, both the links mentions hardware and network issues)

    Does it corrupt the whole record, or say only a Memo field? There are issues with Memo fields, that can be worked around (<grin>one thing that isn't corruption, but if you join on memos, or perhaps group by, then there's a chance the result will display Chinese characters, but who'd join on memos?</grin>).

    How do you deal with the corrupted records? If you don't handle them correct, I think there's a chance the table is still corrupt. If I recall correct, I think the the procedure could involve creating a new table, then through action queries, append all records "below"/"above"/"inbetween" the corrupted records, delete the old table, then reestablish relationships with the new table.
    Roy-Vidar

  9. #9
    Join Date
    Aug 2007
    Posts
    5
    We have no memo fields in the table. The way we ahve it set up is like a queing system. We have multiple analysts getting items to work from a form that goes against the table. I almost get the gut feeling that it has something to do with them maybe getting the same item to work at the same time??? It corrupts the entire line, neg auto number, asian characters, date fields sometimes populate with a date in 1899. This in turn messes up the whole queing system because one of the fields that determines what to work next gets corrupted. We usually compact and repair the database whenever we can.

  10. #10
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    And the other things I mentioned explicitly, "Name Autocorrupt", split (FE/BE), SPs and how to you "fix" the corruption?
    Roy-Vidar

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Unless the NSA has somehow become involved, Foreign Characters = Corruption!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    > Unless the NSA has somehow become involved, Foreign Characters = Corruption!

    I don't know what influence NSA might or might not have, but as I said in my first reply, there are situations where you might get chinese characters with no corruption at all. Allen Browne discusses/demonstrates one such situation here http://groups.google.com/group/micro...022ad3e768669/

    However, we have established that this is corruption, and we are trying to determine what caused it. I'm asking about some of the basic stuff first, which is whether or not the Autocorrupt feature is turned off, whether it's a split setup, and whether one are at the latest SPs. There are more information about what to do, what to check... in the links in my first reply.
    Roy-Vidar

  13. #13
    Join Date
    Aug 2007
    Posts
    5
    We have recently turned off the autocorrect feature, and we don't have it split yet. Normally when we get the characters we remove the primary key, delete the line of corrupt data, put the primary key back on, and then compact and repair the database. We then will goto a historical data table and attempt to refresh the data from a point when it wasn't corrupted. As far as the service packs, I'm not sure if they are current. I also don't have the sufficient permissions on my pc to do any updates of that sort.

  14. #14
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Then I think you should consider splitting it in one backend (BE) which resides on a server - note don't place it "far down the directories", keep the path short/only one level.

    Let each user have their own front end (FE) which links to the backend.

    FE has all the forms, queries, reports, code... BE has only the tables. Create at least one copy before trying There's a wizard somewhere on the Tools menu, I think, which will help, or you could just import all the objects to a new database, then use File | Get external Data | Link... to link.

    As I said, not splitting the app, is inviting corruption, I'm afraid.

    Also, I'm not sure simply removing the "bad data" from the table is sufficient, but do try the above, and see whether that helps.
    Roy-Vidar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •