When I import an Excel worksheet into Access, it automatically assigns each column a data type based on what is in the first couple of rows and independent of what the Access table field type is.
In my case I have a worksheet I'm importing using the TransferSpreadsheet command but when it imports the data, one of the columns fails to import and yields a "Type Conversion Failure". The field is formatted as "General" in Excel and as "Short Text" in Access 2016. If I format all the data in Excel as text then it imports okay.
The first few entries are numbers so it appears Excel assumes the whole column data type is numerical and any non-numeric entries generates the import error table with the Type Conversion Failure entries.
My question is : Is there a way to prevent Excel from auto-assigning a field data type and causing data loss when importing to Access? Or do I have to manually change the data in Excel to prevent that from happening?