Memo vs. Text datatype

[Slez via AccessMonster.com]

I saw in some previous posts that a Memo field can be a cause of corruption
because it is not actually stored in that given table, and that some people
avoid Memo fields if possible. I have experienced this corruption on a
couple of occasions, so I'd like to pose this question.

I have a couple of fields where I regularly need to enter/store up to 1000
characters. The Text datatype allows only 255 chanracters, while the Memoo
allows 65,535, which for my application is vastly overkill.

Is there a workaround that would allow 1000 charactters and help reduce the
chances of that field corrupting? Just looking for any thoughts on the topic.
Any responses are appreciated!

Thanks in advance!
Slez

 

[Jerry Whittle]

I also avoid memo fields when possible.

I've seen people attempt to link together multiple text fields, but that is
very troublesome plus you might bump into the the 2,000 character limit per
record.

You could hyperlink to a Word or text document. I'd recommend that if there
aren't very many records.

You could switch the type of database back end holding the tables. For
example a Varchar field in SQL Server/Access Project handles 8,000
characters.

At the minimum makes sure that your database is split and do frequent backups.

 

[Allen Browne]

Slez, there was a bug back in the early days of Access 97 where this kind of
corruption was more common. Those who were bitten became shy of memo fields.
That particular bug was fixed long ago (in one of the A97 SPs.)

Since then, the corruption occurs rarely. If you are seeing it with any
regularity, IMHO you would be better to focus your efforts on what's causing
it rather than developing workarounds. Typically it's an interrupted write
that triggers the corruption, and the most common cause of that is probably
a bad network connection (bad card/cable/WiFi interference), power (brown
outs, ...) or pc/software/user that crashes (so Access doesn't close
normally.) Tracing it may involve logging users in and out as they open and
close your application, and then seeing which users/machines are crashing
out of the app. The API call to silently get the user name is here:
http://www.mvps.org/access/api/api0008.htm
and the computer name is here:
http://www.mvps.org/access/api/api0009.htm

So, while it makes sense to use memos only where applicable, IMHO it is not
necessary to avoid them.

 

[Slez via AccessMonster.com]

Thanks for the replies! This is a very rare occurrence and the insight sure
helps. We have been told our cabling is in need of an upgrade, and I'm
willing to bet that is the most likely cause.

Again...I sure appreciate the feedback!
Slez

Slez, there was a bug back in the early days of Access 97 where this kind of
corruption was more common. Those who were bitten became shy of memo fields.
That particular bug was fixed long ago (in one of the A97 SPs.)

Since then, the corruption occurs rarely. If you are seeing it with any
regularity, IMHO you would be better to focus your efforts on what's causing
it rather than developing workarounds. Typically it's an interrupted write
that triggers the corruption, and the most common cause of that is probably
a bad network connection (bad card/cable/WiFi interference), power (brown
outs, ...) or pc/software/user that crashes (so Access doesn't close
normally.) Tracing it may involve logging users in and out as they open and
close your application, and then seeing which users/machines are crashing
out of the app. The API call to silently get the user name is here:
http://www.mvps.org/access/api/api0008.htm
and the computer name is here:
http://www.mvps.org/access/api/api0009.htm

So, while it makes sense to use memos only where applicable, IMHO it is not
necessary to avoid them.

I saw in some previous posts that a Memo field can be a cause of corruption
because it is not actually stored in that given table, and that some

[quoted text clipped - 15 lines]

Thanks in advance!
Slez

 

[Armen Stein]

Slez, there was a bug back in the early days of Access 97 where this kind of
corruption was more common. Those who were bitten became shy of memo fields.
That particular bug was fixed long ago (in one of the A97 SPs.)

Since then, the corruption occurs rarely. If you are seeing it with any
regularity, IMHO you would be better to focus your efforts on what's causing
it rather than developing workarounds. Typically it's an interrupted write
that triggers the corruption, and the most common cause of that is probably
a bad network connection (bad card/cable/WiFi interference), power (brown
outs, ...) or pc/software/user that crashes (so Access doesn't close
normally.) Tracing it may involve logging users in and out as they open and
close your application, and then seeing which users/machines are crashing
out of the app. The API call to silently get the user name is here:
http://www.mvps.org/access/api/api0008.htm
and the computer name is here:
http://www.mvps.org/access/api/api0009.htm

So, while it makes sense to use memos only where applicable, IMHO it is not
necessary to avoid them.

I agree. We use Memo fields when we need them. We follow the other
best practices. Our clients almost never experience any corruption.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com