Long Text only accepting 255 characters

[David Ball]

Hi,

I have a table that I update by pasting in the contents of an Excel spreadsheet. Some of the entries in the Comments field are quite long.

I have set this field to Long Text (no option for Memo) but it is still not capturing everything, only showing 255 characters.

I tried changing to Rich Text, as I saw suggested somewhere, but this has not made any difference.

Is there a way that I can get this field to accept all the text that is in the spreadsheet?

Thanks very much

Dave

 

[sneuberg]

Try using Excel automation to read the cell directly into a string variable and then try inserting that into a long text field using a recordset.

 

[David Ball]

Thanks, but that's way over my head

 

[JHB]

Are you inserting it direct in the table or by a control on a form?

 

[sneuberg]

Thanks, but that's way over my head

The simple code below which is demonstrated in the attached database inserts text from cell A1 of a spreadsheet into a large text field. If this is over your head I suggest learning VBA. Try this Web site to get started.

Private Sub AddExcelData()

Dim my_xl_app As Object
Dim my_xl_worksheet As Object
Dim my_xl_workbook As Object
Set my_xl_app = CreateObject("Excel.Application")
Dim strLargeText As String
   
'change path as required
Set my_xl_workbook = my_xl_app.Workbooks.Open("C:\Users\sneuberg\Desktop\Book1.xlsx")
Set my_xl_worksheet = my_xl_workbook.Worksheets(1)
strLargeText = my_xl_workbook.Sheets(1).Range("A1")
CurrentDb.Execute "INSERT INTO [The Table Name] ( LargeTextField ) VALUES('" & strLargeText & "')"
my_xl_workbook.Close
Set my_xl_app = Nothing

End Sub

 

[sneuberg]

Actually I tried copying and pasting a large amount of text from a spreadsheet into the form that's in the database I uploaded and it worked fine. So I suggest you check to see how your situation is different.

 

[David Ball]

Thanks sneuberg, I paste the Excel data straight into a Table

 

[sneuberg]

Thanks sneuberg, I paste the Excel data straight into a Table

I find I can do that too in the database I attached.

 

[JHB]

I find I can do that too in the database I attached.

Where do you attached it?

 

[sneuberg]

Where do you attached it?

Oops. It was supposed to be attached to post #5. Sorry about that. It's attached to post #5 now along with the spreadsheet I was using to test this.

 

[David Ball]

I still have the same problem. I have a total of 32 columns of data that I paste into my table from an Excel spreadsheet.

The column I am having problems with is called Comments. If I paste text directly into the Comments field for a particular record I can paste over 600 characters no problem at all.

But when I paste the entire spreadsheet in (all 32 columns together) it only accepts 255 characters in the Comments field.

All other data comes in perfectly, it just doesn't allow more than 255 characters. My table is set up exactly the same as sneuberg's example database.

Is there anything I can do?

Thanks very much

Dave

 

[static]

Insert a dummy record into the spreadsheet before the record you want to begin copying from.
Add dummy data that matches the datatypes of your Access table and add text with > 255 chars to your Comments column.

Copy the dummy row along with the rest of the data.
Delete the dummy record from Access.