Memo datatype and DAO

[colofnature]

Hi all

I've posted this in my usual Excel forum without any response so I figure someone here might be able to help. I'm using an SQL query in an Excel VBA macro to extract info from an Access table and dump it into an Excel worksheet, which works fine except for a Memo field, which is truncated to one character length - text fields are fine, just this one Memo field. I'm using the Microsoft DAO 3.6 library. Any ideas?

Cheers
Col

 

[Geoff Convery]

Depends how much of the memo field you need. If the first so many characters are enough have you tried defining the target Excel column specifically as a maximum size text column (I'm not too hot on Excel so I don't know what the maximum size is, but I guess 255 characters like Access). My guess is that Excel doesn't recognise what the source column is and that an Excel column isn't big enough to to hold the entire text so it just gives up. You'll end up with the text truncated if there's a lot of text in the memo field but I think that'll happen what ever you do.

 

[colofnature]

Thanks Geoff - didn't fix the problem but your suggestion is appreciated.

It seems to be a problem with the CopyFromRecordset method in the DAO library, as the field in the recordset at runtime holds the full text, it's just when it's dumped into Excel that it's truncated. Not ideal, but at least I can work around it...