VBA Excel SQL Query limits fields to 255 characters

Question

When doing a simple select * query to an excel workbook, it cuts off text after 255 characters. Are recordset fields limited to 255 characters? if not, how can I get the full field text from the recordset?

The test line should have over 1400 characters All other line items are less than 255 characters, and are correct.

Sub ExportCallLogs()

Dim conn As Object
Dim rs As Object
Dim dbpath As String

' Create the connection string.
dbpath = "mypathhere.xlsx"

' Create the Connection and Recordset objects.
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' Open the connection and execute.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
conn.Open sConnString
Set rs = conn.Execute("SELECT * FROM [Sheet1$]")

' Check we have data.
If rs.EOF Then
    MsgBox "Error: No records returned.", vbCritical
    Exit Sub
End If

'print headers and recordset
Workbooks.Add
For i = 0 To rs.Fields.Count - 1
    Cells(1, i + 1).Value = rs.Fields(i).Name
Next
Range("A2").CopyFromRecordset rs

Columns.AutoFit
Columns(i).ColumnWidth = 55

' Clean up
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

Answer 1

Found the very weird problem, and solution. This ended up working for exporting data through a query and inserting data.

This is an issue with the Jet OLEDB provider. It looks at the first 8 rows of the spreadsheet to determine the data type in each column. If the column does not contain a field value over 256 characters in the first 8 rows, then it assumes the data type is text, which has a character limit of 256. The following KB article has more information on this issue: http://support.microsoft.com/kb/281517