asked on

How to force Access to scan entire imported file to determine field type?

I am importing Excel worksheets into an existing Access table and am having problems with the text being imported into a memo field being truncated at 255 characters. I think this is because Access scans the first 25 characters of the imported field to determine the field type and many of the Excel files being imported have short text fields in the first 25 rows so Access determines that it should import the field as a text field rather than a memo field. I seem to recall there being a way to force Access to scan the entire Excel file during the import to determine field type (not just the first 25 rows), but I can't remember how that was done. Am I correct in recalling that is possible, and if so, how is it done?

Amit Khilnaney

Enclosing image from following resource..

You need to select the data type accordingly

For more See this

http://www.addictivetips.com/microsoft-office/access-2010-import-worksheet-from-excel-2010/
specifyfields2.png

marknlynn3

ASKER

I am importing data into an existing table so the field types have already been defined, and they can't be altered with the import wizard. Access is scanning the first 25 rows during input to determine field types and even though the field it is importing to is defined as a memo field, it determines that since the first 25 rows had text fields of length 255 characters or less that all the records must be less than or equal to 255 characters and it truncates records with more text than that. I believe there is a Access setting that can be changed so that Access evaluates the entire file when determining field types during import. That's what I'm searching for. Otherwise, I will have to move a record with a lot of text to be the first record in each Excel file to be imported, which I would prefer not to have to do for each imported file.

SOLUTION

Amit Khilnaney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

marknlynn3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial