CopyFromRecordset fails if string > 911?

[Ken Valenti]

When using the CopyFromRecordset method, it fails if the lenghth of text
exceeds 9XX.

Using ADO to connect to an Access 2003 database. Fields are defined as
"memo" in Access (unlimitted length).

 

[Tom Ogilvy]

Sounds like this might be a limitation in Excel:

http://support.microsoft.com/kb/818808/en-us

You may receive a "Run-time error 1004" error message when you
programmatically set a large array string to a range in Excel 2003

 

[Lazzaroni]

I also ran into the 911 character limitation mentioned in KB 818808 too.
There has to be a workaround, however, since using Tools > Import External
Data or Microsoft Query does not produce the same error using the same data.

I attempted to use the copy recordset to array, then transpose and copy
array to worksheet method mentioned in KB 246335, but I got the same error.
That makes sense since, as KB 818808 states, the error is produced by
"programmatically set[ting] a large array string to a range in Excel 2003."

So, the question remains, how does Tools > Import External Data do it and
not get the error?

 

[Lazzaroni]

Started up a new post at the following link. This one wasn't getting any
responses.

http://support.microsoft.com/newsgr...99e-59acc9ed66c3&lang=en&cr=US&sloc=en-us&p=1

 

[Lazzaroni]

I posted what I think is an elegant solution to the 911 character limitation
in Excel. Perhaps it will work in Access as well.

http://support.microsoft.com/newsgr...99e-59acc9ed66c3&lang=en&cr=US&sloc=en-us&p=1