Solved

CopyFromRecordset error - burping on memo field size when exporting to Excel (using ADO)

Posted on 2006-07-12
2
1,241 Views
Last Modified: 2012-08-13
I'm running a ADP against SQL Server 2000 using ADO

A query builder assembles the sql string and sends it off to a routine that exports to EXCEL.

I've tried the alternate method: docmd.TransferSpreadsheet.
The query string appears to be too long for TransferSpreadsheet to handle, so I'm stuck using the CopyFromRecordset method.  (The third method - parsing through each row of data - is too painful to contemplate as the record count is quite large).

Here's the export to excel code:
=================
Public Function ExportToExcel(strsql As String) As Boolean
If Nz(strsql, "") <> "" Then
On Error GoTo ExportToExcel_Err
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim gobjExcel As Excel.Application
    Dim introwcnt As Integer
    Dim intcolcnt As Integer
    Dim rng As Excel.Range
    Dim objExWS As Excel.Worksheet
    Set gobjExcel = New Excel.Application

    Dim fld As ADODB.Field
    DoCmd.Hourglass True


    rs.Open strsql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If rs.RecordCount <= 5000 Then  ' forces user to limit size of export
        If Not rs.EOF Then
                gobjExcel.Workbooks.Add
                Set objExWS = gobjExcel.ActiveSheet
                introwcnt = 1
                intcolcnt = 1
                 For Each fld In rs.Fields
                     If fld.Type <> adLongVarBinary Then
                         objExWS.Cells(1, intcolcnt).Value = fld.Name
                         intcolcnt = intcolcnt + 1
                     End If
                 Next fld
                 objExWS.Range("A2").CopyFromRecordset rs, 5000
                 gobjExcel.Selection.CurrentRegion.Columns.AutoFit
                 gobjExcel.Selection.CurrentRegion.Rows.AutoFit

                 gobjExcel.Visible = True
                 ExportToExcel = True
        End If
    Else
        ExportToExcel = False
        MsgBox ("You cannot export more than 5000 records to Excel. Please refine your query.")
    End If
    rs.Close

ExportToExcel_Exit:
    Set rs = Nothing
    Set fld = Nothing
    Set rng = Nothing
    Set objExWS = Nothing
    DoCmd.Hourglass False
    Exit Function

ExportToExcel_Err:
    MsgBox Err.Description
    ExportToExcel = False
    Resume ExportToExcel_Exit

End If

End Function
===============

Here's a memo field entry that causes the method to fail:
===============
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Lindberg Budget.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Misc Info.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Mkt Data REIS.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt AP 05.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt AR 05 Misc.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt Dunhill 03.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt Dunhill 05.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt Dunhill 06.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt Dunhill Sched E 02.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Op Stmt Dunhill Sched E 03.pdf;
===============

Here's a memo field entry that successfully exports to Excel using the above code:
===============
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\Tx Rtn Transc Lindberg 02-05.pdf;
\\hcc-filer\SharedDocuments\Hometown\Originations\Deals\Deal_ID_775_Dunhill_Holdings\UCC Stmts.pdf;
===============

The only difference is the length of the data in the memo field of the record.

My environment:
==========
Microsoft Office 2003 SP2
XP Pro
Sql Server 2000
===========


Any ideas???
0
Comment
Question by:Hometowncc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17096512
I would go back to the transfer spreadsheet options.

Create a queryDef object with the name of "" (means is not a permenant one) - pass the query string to the querydef and then pass the querydef to the transferspreadsheet - because the querydef is compiled and has a larger allocation for sql size it should not present any problems.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17097957
Ok, from what I gather, Excel has a limit of 1024 characters in a cell (display), but 32767 chars in total. But it does depend on what version u have. Earlier versions had a limit of 255 - http://office.microsoft.com/en-us/assistance/HP051992911033.aspx
But this I dont believe is the problem

I thought it may have more to do with CopyFromRecordset - http://support.microsoft.com/kb/246335/

Your large memo line as 1101
Your small memo line is 220

So I thought of changing code to not use RecordSet, basically create our own kind of Recordset

dim iCol as Integer
Dim iRow as long
iRow = 2

    Do While rs.EOF = False
   
        For iCol = 0 To rs.Fields.Count - 1
            objExWS.Cells(Row, iCol + 1).Value = rs.Fields(iCol).Value
        Next iCol
        iRow = iRow + 1
   
        rs.MoveNext
    Loop


And this works fine!

So perhaps this is what u will have to do





0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question