More on corruption (2000 sr uncertain)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

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 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

Reply | Quote

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

Reply | Quote

Amen, Charlotte! 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.

Reply | Quote

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

<>

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

Reply | Quote