• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking Excel file to MS Access - field type is incorrect in Access

A

aseltman

New Member
I have an access database and have linked some excel files to the database. I then run queries using the linked Access tables to append data to another table in Access. However, the field type is not always correct for fields that sometimes have a large amount of data. They should be long text instead of short text (but Access is making them short text and cutting off data in some of the fields). Any suggestions to resolve?
 
Hi Alan. The data in a linked table that is linked to an excel spreadsheet is not limited to 255. It looks at the first 8 rows for that field and determines the field type. I actually did a test with a few rows in the excel file where I know the field is over 255 and it worked. It made the field Long Text and did not truncate the field. However, I can't re-sort the excel file or change the order of the rows so the first 8 rows do not have over 255 characters of data in the excel.

This is something that needs to happen every week. It is a very large Excel spreadsheet. The column names have spaces and change regularly. It would be a nightmare to try to import the file every single week with all the cleanup that is needed.

Any other suggestions?
 
Can you supply a sample workbook that we can work with to suggest a potential solution. What I provided you was directly from Microsoft. Did you click on the link and see what they said about the issue. The thread above is not my answer but Microsofts.
 
Yes. I looked at the link. I have come up with my own work-a-round for now. Not the best, but it works.
 
You must log in or register to reply here.
Back
Top