Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More on corruption (2000 sr uncertain)

    We have a database which appears to be getting corrupted on an increasingly regular basis. I've checked other posts here on the subject but have a few more questions I'm trying to clear up.

    This is our setup:

    A single database i.e. NOT split into an application front end and a data back end (the database was originally split but someone throught it best to rejoin them as the application part really only comprised 3 simple forms and a report)
    9 tables, most with at least 1 memo field (upto 17 memo fields in one table - some of these fields are storing a single text character).
    Approximately 200 records and 500MB in size (recently compacted)

    We have had some crashes, possible caused by network problems and someone suggested a pcl 6 printer driver could be contributing to this instability.
    Following a crash the database wont open and needs repairing - so far Access has successfully managed to repair the database

    My thinking, after reading posts in the lounge is that a memo field is becoming corrupted. My problem is how can I check for corrupted records?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More on corruption (2000 sr uncertain)

    With up to 17 memo fields in one table, I'm surprised your corruption problems aren't *worse*. What on earth could require 17 memo fields in a table? I've found that memo fields are very rarely actually required and are usually used for the wrong reasons.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: More on corruption (2000 sr uncertain)

    Amen, Charlotte! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> We do have a rare case where we have 2 or 3 memo fields - but NEVER 17. And a database that 500MB with 200 records is definitely outta control. It sounds to me like you may want to extract everything into a new database and then see what size it is. I could store a bunch of audio files in something that size. We only use memo fields when the user swears that they can't get by with less than 4 sentences of text, and then we grouse about it.

    Actually, we tend to use SQL Server tables for these kind of tables and they don't seem to corrupt. That likely isn't a choice for you, but you might want to think in those kind of terms down the road. A split database will help some with your corruption problem, but anyone who crashes for any reason while they have one of the records open for editing is likely to corrupt things. You may want to go back and take a hard look at the table design.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: More on corruption (2000 sr uncertain)

    First of all, hear hear re the comments on memo fields.

    << My problem is how can I check for corrupted records? >>

    What I have found in the past when I had to try and find bad records in a table which had corrupt memo fields was to manually go thru the table one record at a time, what you will find is Access will throw up an error dialog box (cannot remember what it says) each time it encounters a bad record.

    All you can do with these bad records is delete them and add new ones in their place, this however can be a rather time consuming task.
    I wrote some code to read from the corrupted table and create another table on the fly trapping any of the bad records by the On Error command.

    I know you probably did not want to hear all this.
    Pat

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More on corruption (2000 sr uncertain)

    Thanks All.

    As ever a this is the place to come for sensible advice.

    The database was written originally by myself in a blinding hurry for a specific job. It was pretty much the first database I did and it did the job, after a fashion.

    Now, I think being the one responsible should've given me the right to make it clear that I thought it was inappropriate to use the same design again, let alone start 'improving' it. Unfortunately my opinion wasn't wanted and a monster was created <img src=/S/electric.gif border=0 alt=electric width=15 height=15>

    Hence, said database was 'modified' beyond all recognition, being merged back together, with umpteen text fields changed to memos (ours it not to reason why) and the addition of an inappropriate number of OLE fields for storing drawings, bits of spreadsheets etc (these are the reason its got so big).

    In a word, I wish I could wash my hands of it <img src=/S/flee.gif border=0 alt=flee width=25 height=25> but I get the feeling things are going to get a lot worse befor they get better. Still, it should provide lots of work if all the tables need checking to look for corruption <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More on corruption (2000 sr uncertain)

    Hi Darsha

    What are your corrupption symptons?

    I had problems with memo fields after a conversion, see post 90138

    bottom line

    Charlotte Said

    Do those fields really need to be longer than 255 characters? If not, change the memo fields to text fields and save yourself some headaches. It will reduce the size of your database as well as the incidences of corruption.

    You might try doing a query to return all records where the Len(Trim(fieldname)) of your memo field is >255 (where fieldname represents the actual name of your memo field). That will at least tell you how many of them really need to be memo fields. If the query doesn't return any records, you would be safe in converting the field to a text field. If you're currently testing to see if the memo fields hold nothing but one or more spaces, try replacing them with a null when Len(Trim(fieldname))=0.

    Charlotte
    Moderator: Access, VB/VBA, Books, General Office Solutions

    My solution seem to work

    UPDATE tblBPermit SET tblBPermit.memConNote = Null
    WHERE (((tblBPermit.memConNote)=" "));

    HTH

    John

Posting Permissions

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