Problem exporting with long strings to Excel using getrows and an array

[Mark]

Hi

Using Access and Excel XP

I have a generic function which I use to dump a recordset into Excel and
apply a little formating etc. This has worked for years without issue.

On my development machine (which is isolated and does not have the latest
service packs applied - naughty I know) I have successfully dumped
recordsets containing strings as long as 1500 characters (I haven't bothered
trying to test for a limit as it works fine for what we handle). On the user
machines (which are connected to the internet and have all the latest
service packs etc. automatically installed) if the recordset contains any
string longer than 900 or so characters we get an "Application defined or
object defined error".

Does anyone know whats going on here? I did some research and found similar
problems, though slightly different, going back as far as Excel 97.

All the functionality for manipulating the recordset and subsequent array
(including the transposeDim function) is handled in Access.

I can post some code if anyone thinks this would help.

Thanks

Mark

 

[RoyVidar]

Hi

Using Access and Excel XP

I have a generic function which I use to dump a recordset into Excel
and apply a little formating etc. This has worked for years without
issue.

On my development machine (which is isolated and does not have the
latest service packs applied - naughty I know) I have successfully
dumped recordsets containing strings as long as 1500 characters (I
haven't bothered trying to test for a limit as it works fine for
what we handle). On the user machines (which are connected to the
internet and have all the latest service packs etc. automatically
installed) if the recordset contains any string longer than 900 or
so characters we get an "Application defined or object defined
error".

Does anyone know whats going on here? I did some research and found
similar problems, though slightly different, going back as far as
Excel 97.

All the functionality for manipulating the recordset and subsequent
array (including the transposeDim function) is handled in Access.

I can post some code if anyone thinks this would help.

Thanks

Mark

I've seen something similar, but I don't know the excact why, sorry.

Using the CopyFromRecordset method, I found that in the 2000 version of
Access, it didn't fail until the memofield contained more than 65 509
characters, but didn't actually copy more than 1823 into Excel, while
in 2003, it failed with the error you mentione at more than 911
characters.

For me it was versions, but it could very well be SPs. I think one
"solution" could be to use Left(MemoField,911) in the query, if that's
sufficient (I don't think Excel cells will display more than 1024
characters anyway), or drop the memofield from the recordset you're
using CopyFromRecordset with, then open a new recordset afterwards and
loop records/cells.

Would be interesting to know, though, what really causes this, and if
there are better workarounds.

 

[Mark]

Thanks for that. It is pretty strange indeed. Truncating strings or dropping
certain fields is not really an option. In this particular case the data
dump is provided so that users can generate ad-hoc reports etc. and the data
in the problematic column is crucial to the analysis.

Thanks again

Mark