Access Excel Import Truncates Text Cells at 255 Chars

codequest
codequest used Ask the Experts™
on
http://support.microsoft.com/kb/839785#appliesto

         Call DoCmd.DeleteObject(acTable, "Test")

        Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, _
        "Test", fullPath, True)

1) I ran this vba in the attached test docs and it DOES work (does not truncate)
2) I changed the target table name in my main app, aimed at my "real" spreadsheet, and it DOES truncate
3) When I run this in my attached test app on my "real" spreadsheet it DOES truncate.  
4)  I stripped everything out of my real xls, except the one row that has a cell that's bigger than 255 char, and leaving the header rows, and it DOES NOT Truncate

SO, there's something in my "real" spreadsheet, independent of the Access app, and not in the row with the one big cell, that causes the data to truncate at 255 chars.  There's nothing in the design or processing of the real xls  that makes me think that that row is any different than any other row.

Unfortunately I can't share my full "real" spreadsheet.   I'm hoping someone has observed this behavior before and knows what might be going on.

Any help with this would be appreciated.

Thanks!
db1.mdb
test1.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The maximum length of a TEXT field in Access is 255 characters. If you need to store data larger than that, use a MEMO data type.
Commented:
If the large field is in the first several records (less than 6 normally, Access accepts that as the data type and makes the field memo.  If the large field is further down the spreadsheet, the field will be typed as text and become truncated.
Commented:
Unfortunately, you do not have any control over the conversion of an Excel file directly to a table.  If, however, you convert the .xls file to a .css file and then convert the .css file, you regain control as to what and how big the fields are to be.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
.css -> .csv  -  sorry!

Author

Commented:
Thanks for input.  GRayL, a) what kind of control would I get if I converted to csv, b) how (generally) could I do the conversion to csv using vba, and c) do you think it would produce a better/easier result than

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23218825.html?sfQueryTermInfo=1+append+excel+exist+tabl
Most Valuable Expert 2014
Commented:
What if it was a linked table instead? Then it could be handled as an RS to the destination table.

Or is it the same issue if the records are past the first X number of records?

Author

Commented:
I settled on a variant of the recordset method in the link above, attached.

I thought it needed the "chunk it out" approach that is shown in the attached but in later tests, proved not so, so I'm not sure what I was seeing.

Thanks for inputs!
db1.mdb
test2.xls

Commented:
Thanks, glad to help.
Most Valuable Expert 2014

Commented:
Glad to be of assistance. May all your days get brighter and brighter.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial