I wanted to ask around about different way to approach handling any data type that are mapped to Access's Memo data type (e.g. LONGTEXT for example) from ODBC end.

Jet's behavior is to only fetch such data when it has to actually display on the screen, and will not fill it up in background as it does with the rest of recordset. That's good behavior, I'd think. :)

So, Microsoft would recommend having a button to display the text if user needs to view the data to help improve general performance. I am not so big fan of asking my users to click more to make computer go faster.

I kicked around some ideas such as truncating to 255 character and thus passing off as a VARCHAR(255) data type = Text data type, but feel that entails a bit excessive hammering of the server.

One thing I have on list to experiment is whether a continuous form displaying only one record at a time would behave just like a single form and actually fetch the data in LONGTEXT when it is painted on the screen, though I would think single form is preferable in most of cases.

Anyway, I'm interested in hearing from others how they provide the users with access to any data type that are mapped to Memo data type (and to an extent, OLE Object, since they behave pretty much same).

*bump*

Noted some posts here and there claiming that if Access deals with Memo-type, there is a higher risk of corruption, even with ODBC, but doesn't seem to be backed up by KB articles or the like.

Has anyone ever experienced corruption specifically because of memos?

Any extra information about ODBC's handling memos and optimizations will be greatly appreciated. :)

I have specifically NOT had problems with memo fields, but have not used them much. If a field won't fit in 255 bytes, I'm thinking there's something wrong with the project charter. Sorry.

Thanks for sharing, George.

My database revolves around case management, so there's lot of documentations that easily exceed 255 character.

I don't intend to do any searching, indexing or any of stuff (with exception for displaying a history for my end users to follow the documentation), but wanted to make sure I did my homework. :)