I've read all the previous posts on this problem; but none of them seem to specifically address the problem I'm struggling with.
I'm trying to import an Excel Workbook to an Access database, which I've done thousands of times with no difficulty whatsoever (though never in Access 2016 until now); but no matter what I do I'm getting type conversion errors on certain fields.
I understand that Access tries to "guess" the field data type by looking at the first row of data in the spreadsheet, and that it can be wrong at times. I'm pre-defining the fields in question as "short text", rather than "number", because though both fields have a numeric-looking value in their first row, they both also have rows where the value needs a "short text" data type. I would figure that by pre-defining the field data types as "short text", that Access would adhere to this directive; but instead it just overrides my pre-programmed data type and replaces it with a "number" data type, and so the import hits conversion errors for those rows. This also begs the question "Why does Access bother having me instruct it on field data types if it's just going to override my decision anyway?"
The weird thing is, the rows for which the import is getting conversion errors actually contain strictly numeric values. Not one of the errors involves a text value trying to fit into a numeric field. So that makes no sense whatsoever.
Additionally, there are more than 20 other fields in the Excel spreadsheet that have the exact same setup (numeric value in the first row, but some textual values further down), and these fields are not getting conversion errors. For these rows, Access just accepts the "short text" data type I have pre-defined before running the import, and thus both the numeric and text values in those fields live together in perfect harmony. This makes the problem seem totally arbitrary.
If anyone can speak to this, please do. I'm on my second day of a new job; and the last time I used Access this was not an issue. I've spent the entire day battling what a few years ago would have been a two-minute task.
Thank you
- Deek