+ Reply to Thread
Results 1 to 7 of 7

CopyFromRecordset Error If More Than 911 Characters

  1. #1
    Lazzaroni
    Guest

    CopyFromRecordset Error If More Than 911 Characters

    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/newsgro...=en-us&m=1&p=1

  2. #2
    arno
    Guest

    Re: CopyFromRecordset Error If More Than 911 Characters

    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




  3. #3
    Tim Williams
    Guest

    Re: CopyFromRecordset Error If More Than 911 Characters

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

    --
    Tim Williams
    Palo Alto, CA


    "Lazzaroni" <[email protected]> wrote in message news:[email protected]...
    > 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/newsgro...=en-us&m=1&p=1



  4. #4
    Lazzaroni
    Guest

    Re: CopyFromRecordset Error If More Than 911 Characters

    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.

  5. #5
    pfg_qa
    Guest

    Re: CopyFromRecordset Error If More Than 911 Characters

    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" wrote:

    > 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.


  6. #6
    Lazzaroni
    Guest

    Re: CopyFromRecordset Error If More Than 911 Characters

    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

  7. #7
    pfg_qa
    Guest

    Re: CopyFromRecordset Error If More Than 911 Characters

    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.





    "Lazzaroni" wrote:

    > 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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1