gateguard
asked on
Data Truncation importing Excel spreadsheet into Access 2010
Trying to import an excel spreadsheet into a new Access 2010 database.
First I create the fields and make sure they are all memo.
But I get truncation anyway.
See attached. Why can't I make the memo field bigger than 255 characters?
MemoSizeInAccess2010.pdf
Thanks.
First I create the fields and make sure they are all memo.
But I get truncation anyway.
See attached. Why can't I make the memo field bigger than 255 characters?
MemoSizeInAccess2010.pdf
Thanks.
What you are seeing in the Access options window reflects two entirely different characteristics of the database.
The "Default field type" and "Default text field size" are mutually exclusive (they do not relate to each other).
The "Default field type" is just that. When you create a new field in a table, it will automatically default to whatever datatype you have selected in this combo box.
The "Default text field size" is the length that will be assigned to text fields whenever you specify that a field is a Text field. I generally set mine to 50.
As to the truncation of data, Patrick's recommendation to us VBA code is the only way I have found around this shortcoming as well. The challenge there is making sure that each row in your Excel speadsheet contains a unique field that can be used to iterate through the rows of the spreadsheet to capture your long strings and insert them back into the Access table.
The "Default field type" and "Default text field size" are mutually exclusive (they do not relate to each other).
The "Default field type" is just that. When you create a new field in a table, it will automatically default to whatever datatype you have selected in this combo box.
The "Default text field size" is the length that will be assigned to text fields whenever you specify that a field is a Text field. I generally set mine to 50.
As to the truncation of data, Patrick's recommendation to us VBA code is the only way I have found around this shortcoming as well. The challenge there is making sure that each row in your Excel speadsheet contains a unique field that can be used to iterate through the rows of the spreadsheet to capture your long strings and insert them back into the Access table.
ASKER
matthewspatrick, fyed, that makes sense, that it has to be done through VBA code (and thanks fyed for the explanation of the options window).
I am sure both of you are right but is there some link to a Microsoft Technet page (or other page) that definitely says this.
I'm not questioning you, but I'd like to have the source document for my records.
Thanks... and thanks again.
I am sure both of you are right but is there some link to a Microsoft Technet page (or other page) that definitely says this.
I'm not questioning you, but I'd like to have the source document for my records.
Thanks... and thanks again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ended up creating the table structure in advance, in Access, and on the memo field clearing the @ sign on the format option. Then the truncation ended.
Hey i have some problem Row truncartion error it will Occures in Access so please Advise What should i do?
It's a fundamental limitation of the product: the Excel import method truncates at 255 characters.
You will have to use VBA code to bypass this.