Fields with data type 'memo' crash access

When running a report, searching a field, filtering by, or sorting by a field etc. Access will crash when you are working with a field with the memo data type. Other feilds work perfectly. The database structure has been the same for a while, and suddenly Access crashes regularly, but only when working with these specific feilds.

I've tried to research the issue a little bit but since time is a factor here I figure I'd ask you guys. If anyone can help I would really appreciate it.
LVL 5
blacksixAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
Well, I would suspect your memo field got corrupted, possibly becaue more than one person was accessing it at a time. Multi-user environment?
clarkscottCommented:
Memos are saved differently than other field types in tables.  These memos are stored separately with an internal link to the table (that's why a memo can be so large).

You may want to repair your tables occasionally.  Also, I don't think you can sort or filter on a memo.

Scott C.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

jerryb30Commented:
You can indeed filter on a memo field, or string inside the memo field.  But the real issue is that when you open a query with the memo type, unless it is the first record which i corrupted, it will look just fine.  The act of searching, sorting, etc moves focus at some point to the corrupted record, and that is why things go haywire.
Rick_RickardsCommented:
There is a known bug in Access 97 that corrupts memo fields as you've described.  Steps to recover from this situation are detailed below...

1) Open the table in design view.  Rename the old memo field to a different name and create a new memo field (using the original name) to take its place.

2) Identify the records that have corrupt memo fields.  They can be spotted by paging though the table and/or running series of queries to isolate the broken records.  Damaged records will contain garbage in the memo field.

3) Build an update query that excludes the damaged records and copies all data from the old memo field to the new one created in step 1.  It would look something like this...

UPDATE tblEmployee SET tblEmployee.Notes = tblEmployee.NotesOld
WHERE ((tblEmployee.EmployeeID) Not In (1,102,105));

4) Delete the old (corrupted) memo field.

Unfortunately the data in the corrupted records is lost, so a backup is your best bet to recover the missing information.

Rick

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blacksixAuthor Commented:
Lot of good information here. This is also a database around the 11mb mark or so, just like the guy in the link jerry posted.

jerryb30: You are right on about the query part, they queries come up fine.

I will try to isolate the record and see what I can do to get rid of the corrupt data and get back to you.

Thanks!
jerryb30Commented:
Above all-backup your database.  
blacksixAuthor Commented:
OK we have some good news. Just as Rick said the corrupt entry had a whole bunch of garbage in it. Created a query to move the data from one field to another with the exception of the one record, this went smooth but still didnt resolve the issue, I had to completely delete the record, now everything is running as expected. Thanks for your help guys.
jerryb30Commented:
Thanks.  Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.