CopyFromRecordset Error If More Than 911 Characters

[Lazzaroni]

While attempting to use CopyFromRecordset I ran into the 911 character
limitation mentioned in KB 818808.

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

The article does not provide a workaround, but there must be one since using
Tools > Import External Data or Microsoft Query in Excel does not produce the
same error using the same data. I attempted to copy the recordset to an
array, and then transpose and copy that array to worksheet, which was listed
as a possible method in KB 246335, but I got the same error.

So, the question remains, how does Tools > Import External Data do the same
thing as CopyFromRecordset without getting the 911 character limitation
error? Does anybody know a workaround?

Thanks.

*This post is a replacement for the post at the following link. The old post
wasn't getting any responses.

CopyFromRecordset fails if string > 911? in Excel Programming

http://support.microsoft.com/newsgr...2c6ee77837e1&lang=en&cr=US&sloc=en-us&m=1&p=1

 

[arno]

Hi Lazzaroni,

The article does not provide a workaround, but there must be one
since using Tools > Import External Data or Microsoft Query in Excel

then you have to use the import function or ms-query. I think, There's
no way to do this with copyfromrecordset. I have the same problem with
memofields from foxpro databases, however, I simply cut only a hundred
characters as I do not need to know the content of these fields and
this avoids the error.

arno

 

[Tim Williams]

Have you tried manually looping through the records and writing the values?

--
Tim Williams
Palo Alto, CA

While attempting to use CopyFromRecordset I ran into the 911 character
limitation mentioned in KB 818808.

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

The article does not provide a workaround, but there must be one since using
Tools > Import External Data or Microsoft Query in Excel does not produce the
same error using the same data. I attempted to copy the recordset to an
array, and then transpose and copy that array to worksheet, which was listed
as a possible method in KB 246335, but I got the same error.

So, the question remains, how does Tools > Import External Data do the same
thing as CopyFromRecordset without getting the 911 character limitation
error? Does anybody know a workaround?

Thanks.

*This post is a replacement for the post at the following link. The old post
wasn't getting any responses.

CopyFromRecordset fails if string > 911? in Excel Programming

http://support.microsoft.com/newsgr...2c6ee77837e1&lang=en&cr=US&sloc=en-us&m=1&p=1

 

[Lazzaroni]

Actually, you may have been on to something here. Using standalone MS Query
is not acceptable because I need to build the query process into my
application. I can, however, utilize MS Query programmatically through the
QueryTables collection in VBA.

ActiveSheet.QueryTables.Add

I'm hopeful that this will bypass the 911 character per cell limitation
imposed by Excel.

I will repost when I have had a chance to try out this workaround.

Thanks.

 

[pfg_qa]

I just hit the same issue, but with a twist:

I've got an ADO recordset that's created from a SQL Server proc. The
recordset returned is based on some passed in criteria. There are 9 fields
in the recordset passed in to CopyFromRecordset. Two of those fields are
defined as varchar(1000). But only ONE of them seems to be causing a problem
and, to further my pain, only SOME of the time. That is, depending on the
criteria passed in, one resultset will throw the error, another won't.
Adjusting the problematic field down to varchar(900) fixes the problem, but I
still have that other field at varchar(1000) that ALWAYS works.

Aaargh. Any ideas on this new angle?
x.

 

[Lazzaroni]

Excel 911 character limitation problem solved.

ActiveSheet.QueryTables.Add works as well, or better than CopyFromRecordset,
and there is no 911 character limitation. QueryTables.Add has the added bonus
of giving you programmatic access to all the features of MS Query, while
still operating entirely in the background.

In the following code oQueryName and oSQL are generated earlier on in my
application. One nice feature of using QueryTables.Add is that if you don't
delete the QueryTable that is associated with the data copied to the sheet,
the query can be easily updated by the user by clicking the Refresh button on
the "External Data" toolbar in Excel. The toolbar automatically appears when
a new QueryTable is created in a sheet.

For my application, I deleted the QueryTable so as not to distract the user
with the refresh button.

Dim oQueryName As String 'this is a unique query identifier
Dim oSQL As String 'this is the query statement

' I left in the original CopyFromRecordset statement
'Cells(1, 1).CopyFromRecordset oRecordset

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MyODBC", _
Destination:=Range("A1"))
.CommandText = oSQL
.Name = oQueryName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
.EnableRefresh = False 'this is NOT equiv to unchecking "Save query
definition."
End With

'delete the query table. this is equivalent to unchecking "Save query
definition."
ActiveSheet.QueryTables.Item(oQueryName).Delete

 

[pfg_qa]

Perfect. I was able to use the ADO.Recordset I was already passing in to
CopyFromRecordset as the Connection parameter. My code automates Excel from
within Access. I did have problems intially with Run-time error '462': The
remote server machine does not exist or is unavailable. It's explained as
part of KB 178510. Explicitly using the Excel object I previously
instatiated to get at ActiveSheet and the Destination range solved it.

"XL" is the Excel Application Object. "TheData" is the ADO.Recordset.

With XL.Worksheets(1).QueryTables.Add( _
Connection:=TheData, _
Destination:=XL.Worksheets(1).Range("A2"))
End With

Thanks for your guidance on this one.
x.