?
Solved

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

Posted on 2006-07-12
2
Medium Priority
?
1,246 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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

801 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