Memo Field Crashes DB

[Clyde]

We have a memo field that returns 182520 from the LEN() function. If you try
to delete some of the older entries you get "not enough memory" and then a
crash of the db.

In fact you might crash by just moving around the memo field.

I would like to delete enough old entries to fix the problem but can't.
Thanks in advance for any ideas.

 

[Jerry Whittle]

Cnacned are the memo fields are corrupt. I'm assuming that you tried a
compact and repair. If so, here's what to try.

1. Make a complete backup of the database mdb file(s) and put away for safe
keeping.

2. Create a query that excludes the particular problem record(s). Include
all fields or use the * wildcard. You need a primary key for this to work so
that you know exactly which record. An autonumber field is best if you don't
already have a PK. After running the query to ensure the proper records are
returned, change it to a Make Table query and run it.

3. Next change the query back to a Select query. This time only search for
the problem record. Make sure that all fields show EXCEPT for the problem
memo field. Run it to make sure. Then change the query to an append query and
append the bad record to the table that you just created.

4. Delete the corrupt table. Rename the new table with the same name as the
old one. You might have problems if there are relationships involved.

5. Create a new database and import everything from the old database. Use
this DB in the future.

 

[Clyde]

Thanks I appreciate the confirmation. I knew I could del the record and
start over. Wanted to avoid because of course there are probably 10 related
tables. Oh well it is what it is.
Thanks again.

 

[aaron.kempf]

wow.. it sounds to me like Jet isn't stable enough for you.

I would reccomend upsizing to SQL Server, you don't lose data when you
use a real database engine