Search code examples
vbams-accessms-access-2013

Long text (> 255 chars) truncated while importing and adding to existing table in MS Access 2013

I am trying to import an excel spreadsheet using the below VBA code. Before bringing it in I am also deleting the current contents of the table.

DoCmd.RunSQL ("DELETE * FROM REBATE_PROG")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "REBATE_PROG", filepath, True

The rebate_prog table already has fields defined as long text (which is the old memo field, as per my understanding) which should be able to store > 255 chars.

Is there a reason why Access would be truncating the contents?

Some of the things that I have tried, without success:

  • Sorting the import file in descending on the length of the field which is truncating - for if excel looks at only a few top rows, it would import as Long text.
  • Tried to update the "Text Format" for the field under Design View in table properties to Rich text from Plain, but for some reason Access doesn't allow me to make the change.

Is there a way I can import the excel spreadsheet without access truncating the fields?

Solution

  • Found a way of fixing this issue. There might be other solutions out there as well but this one seemed to have worked for me the best. Below is a step by step of what I did:

    1. Manually creating an Access Table: I went with manually creating the table structure in access 2013 and defining each data type for each field. On the field that I wanted to have >255 characters imported, I set this field data type as "Long text" and also selected the format to "Rich text".

    Note: The structure I created for this table is exactly as it is in the file I intend on importing.

    1. Sorting Import File on Character length: Next, I added a new field in the import file using LEN() function as LEN(<Cell in the long text field>). And then used this field to sort all records in descending order and saved the file. I had to do this because Access while importing looks at a few top rows to determine how much data needs to be imported from the column (I learned this after going through a few forums).

    Note: You could also create a VBA code which does the sorting in descending order for you right before importing, but I haven't executed that yet. If you do, please do share the code here!

    1. Import!: Finally, use the code I provided below to truncate the table we manually created and insert the contents from excel into the table.

    DoCmd.RunSQL ("DELETE * FROM REBATE_PROG") DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "REBATE_PROG", filepath, True