Solved

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

Posted on 2006-07-12
2
1,225 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
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now