255 Character Limit

[nicolenwn]

Hi,

Happy new year to all(:

I have a Microsoft Access Database which stores comments. Comments are 'Memo' typed. Thus they can be very very long. However i need to export the comments into excel monthly but the 255 character limit refrains me from exporting the entire comments stores in the Access database.

I have tried to find solutions for this as it is a common problem, but to no avail.

Many thanks in advance for the help and to a great year ahead!

Nicole

[nico5038]

When exporting a table without using a query, the 255 limit won't be used by Access. Just when you "manipulate" the field in a query it will be truncated.

There's however an additional limit, that of the excel cell :-(

The only 100% solution I know is to export the table to a Word file (e.g. .rtf) to get all data visible without truncation.

Nic;o)

[missinglinq]

Obviously you can't, with Excel having a column width limit of 255 characters! I think more importantly here is the question of why you feel the need to export it to Excel in the first place! If you're doing it because you need to provide the data to people who don't have Access on their PCs there are other ways to do this.

Welcome to Bytes!

Linq ;0)>

[nico5038]

From Microsoft: "Only 1,024 characters are displayed in a cell in Excel"
http://support.microsoft.com/kb/211580

But it can contain a max of 32,767 characters....
The display problem has been solved in Excel 2007.

Nic;o)

[missinglinq]

I stand corrected, Nic;o) ! The 255 character maximum was for Column Width, which is actually how how wide a column can be, but you can have multiple rows in a column and hence display more characters in a cell.

Doesn't appear to matter, as the OP appears to have lost interest!

Linq ;0)>

[NeoPa]

I expect you're hitting an unnecessary limit here Nicole. As the boys have said, there are many more characters that can be passed without needing to resort to other methods.

Perhaps if your requirement is an unlimited length field, then a different approach is called for. As with any such process, you need to consider the parameters of your project before deciding what is appropriate for the solution. Although Excel can handle many more characters than 255, there are nevertheless length limits which will cause you issues. To find the most appropriate solution we'd need a better understanding of your requirements.

[Stewart Ross]

I was trawling through the many items on the Allen Browne Access web site tonight and found a reference to a Microsoft Knowledgebase article on the 255 character issue when exporting reports (not queries): Memo Field Truncated When Report Is Output to Excel. According to the article, truncation to 255 characters results from Access exporting data in Excel 5.0/95 format, a much earlier version of Excel where the 255 character limit for text in a cell did indeed apply.

It may not matter now that another solution has been found, but it is at least another view on what may have been wrong originally.

(By the way, the second "solution" for this problem proposed in the MS article is just plain silly IMHO - split the memo field into 250 character components and then concatenate them back together again in Excel. How slick!)

-Stewart

[Chris Jaggard]

I came across the same error recently & thought it would have been solved now in Access 2010, but sadly not. The problem exists if using the DoCmd.OutputTo or DoCmd.TransferSpreadsheet methods and you have any table columns with more than 255 characters. My workaround is to write the data to an Excel object instead, rather than splitting fields into 255 chunks which is crazy.

If your data is in a table or simple query, call the SendToExcel function as follows:

SendToExcel "SELECT * FROM MyTable"

Public Function SendToExcel(sSQL As String)
 
    ' Creates a new Excel Workbook/Worksheet & writes the specified data into it.
    ' Note you can't export a parameter query, must create a temp table first in this case.
 
    ' Use Late Binding to prevent need to include Excel Object Library References
    ' thus making it independent of MS Office version
 
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
 
    Dim lvlColumn As Integer
 
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
 
    Set cnn = Application.CurrentProject.Connection
    rst.Open sSQL, cnn
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    xlSheet.Activate
    xlSheet.Application.Visible = True
 
    'Loop through the fileds collection and make each field name a column heading in Excel
    For lvlColumn = 0 To rst.Fields.Count - 1
        xlSheet.Cells(1, lvlColumn + 1).Value = rst.Fields(lvlColumn).Name
    Next
 
    'Change the font to bold for header row
    xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, rst.Fields.Count)).Font.Bold = True
 
    'Send data from Recordset out to Excel
    xlSheet.Range("A2").CopyFromRecordset rst
 
    'clear up objects
    rst.Close
    Set rst = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
 
End Function

If you have a parameterised query however you will need to write it first into a temporary table, then remove this after the export. This may not be the neatest solution but it works.

Call the SendToExcel function instead as follows, where objName is the name of your parameterised query.

Public Function ExportToExcel(objName As String)
 
    ' Export a table/query to Excel by first saving to temp table then writing contents to Excel.
    ' This is to get around the issue of 255 character limit in any field when using the built in
    ' Access functions such as OutputTo or TransferSpreadsheet.
 
    'hide warnings
    DoCmd.SetWarnings False
 
    'Create a temp data table
    DoCmd.RunSQL "SELECT * INTO ExportData FROM [" & objName & "]"
 
    'Write the contents of the table out to Excel
    SendToExcel "SELECT * FROM ExportData"
 
    'Delete the temp Export table
    DoCmd.DeleteObject acTable, "ExportData"
 
    'reset warnings
    DoCmd.SetWarnings True
 
End Function