• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1268
  • Last Modified:

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

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
Hometowncc
Asked:
Hometowncc
1 Solution
 
Raynard7Commented:
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
 
rockiroadsCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now