MEMO field called into excel from SQL query 255 character problem

[tomhoney]

Hey guys,

I have a question regarding MEMO fields being called into Excel from an Access db. I'll provide 2 sections of code. The first one pulls a bunch of information including a MEMO field from 2 tables and pastes the record set into a listbox. This returns all characters from the MEMO field. The second section of code does exactly the same thing except pastes the record set to a cell reference on a worksheet. The latter will not return all the characters (if any) from the MEMO field;

PASTE TO LISTBOX:

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
    Dim MyConn, sSQL As String
    Dim n As Integer
    
    MyConn = "S:\Shared Applications 9\Shared Applications 3\Objective Trackers\Collections\CompAndVal.mdb"
    
    sSQL = "SELECT evidences.EvidID, compsandvals.CompID, compsandvals.SectionName, compsandvals.LevelName, compsandvals.DesName, evidences.EvidDT, evidences.EvidMEM FROM evidences, compsandvals WHERE evidences.StaffID = '" & Worksheets(ActiveSheet.Name).Range("A70").Value & "' AND evidences.CompID = compsandvals.CompID ORDER BY evidences.EvidDT, evidences.EvidID ASC"
    
    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = "S:\Shared Applications 9\Shared Applications 3\Objective Trackers\Collections\CompAndVal.mdb"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With
    n = Rs.RecordCount
    
    'If there are no records in an advisor's comp and vals we need to catch the error it will produce
    On Error GoTo ErrHandler:
    frmSelect.lstCompNo.Column = Rs.GetRows(n)
    frmSelect.lstCompNo.ColumnWidths = "50;0;150;85;200;50;900"
    Rs.Close
    Cn.Close
    Set Rs = Nothing
    Set Cn = Nothing

PASTE TO WORKSHEET:

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String
MyConn = "S:\Shared Applications 9\Shared Applications 3\Objective Trackers\Collections\CompAndVal.mdb"
sSQL = "SELECT evidences.EvidID, compsandvals.CompID, compsandvals.SectionName, compsandvals.LevelName, compsandvals.DesName, evidences.EvidDT, evidences.EvidMEM FROM evidences, compsandvals WHERE evidences.StaffID = '" & Worksheets(ActiveSheet.Name).Range("A70").Value & "' AND evidences.CompID = compsandvals.CompID ORDER BY evidences.EvidDT, evidences.EvidID ASC"
Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = "S:\Shared Applications 9\Shared Applications 3\Objective Trackers\Collections\CompAndVal.mdb"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With

Worksheets("tempPrint").Range("C104").Value = "LIST OF EVIDENCES PROVIDED"
Worksheets("tempPrint").Range("C106").CopyFromRecordset Rs
Rs.Close
Set Rs = Nothing
Cn.Close
Set Cn = Nothing

Now both do exactly the same query, but the first one returns everything to the LISTBOX including the full MEMO field from the DB, but the second returns everything else EXCEPT the MEMO field to the worksheet

If EvidMEM is changed to Left(EvidMEM, 255) then it will show the first 255 characters of the MEMO field when pasted to the Worksheet, but nothing past 255 will be pasted to the worksheet.

This doesn't make any sense to me because the character limit of a cell is around 37,000 with 1024 of them being visible from the sheet.

Can anyone tell me why it pastes the full MEMO into the LISTBOX but not into a Worksheet? Many thanks

Tom