What variable type to use with memo field in VBA

[Rank Am]

Quick question on VBA Access 2003/07,
I have a VBA class module that loads a single record from 1 of 4 tables (via a main form and datasheet subforms) to another form.
Most of the fields are keys however 1 field is memo data type.

What data type is best to use for the variable / property procedure in the class module. String seems to work but are there any limits in string length in vba, or a better way of achieving this. I seem to remember reading somewhere that memo datatype fields the data is not stored in the access db only a reference, is there any way of using this and passing the reference to the text box

Also the data is passed to the form via an ADO recordset am I right in saying that there is no issue with string length in ADO recordsets?

Thanks

Jon

 

[ajetrumpet]

are there any limits in string length in vba,

actually i dont think so

 

[dkinley]

There shouldn't be a limit (except for memory). Now, I've always understood that different interfaces might put a restriction on a string's length (i.e., MsgBox and some Import methods).

For memo fields, I've always loaded those into strings without issues.

-dK

 

[Rank Am]

Thanks for the response guys, sorry about the delay in responding I have been away for the last couple of weeks

 

[LPurvis]

A String can hold 2^31 (approx 2 billion) characters.
Which alligns pretty well with the maximum size of a Memo field data. (The size of the MDB/ACCDB database file).

Such Memo data would, obviously, have to be entered by some means other than the Access UI, where the text limit for a Memo drops to approx 65K characters.

In other words, you're just fine.

The Recordset isn't a consideration either really, other than to consider available memory on your system. You'd want to avoid paging if you can help it. (The thought of moving around billions of characters of text does fill me with dread though - what on earth could that be for? :-s)

Bear in mind that Binary data is a different matter. We'd still be talking huge files though - bigger than anything you'd want to store in a database (the packing/unpacking time would surely make it prohibitive so as to be useful).

Oh, and Memo data is, naturally, still stored in the database file - it has to be, that's all the engine has to work with). You'll have read a misinterpretation of the fact that Memo data is not stored on the same data page as the other table data. (The pages are only 4K, so it very often couldn't possibly be).
Pointers are indeed used to other data pages. But there's no way - or need - to consume that implementation.

Cheers.