Microsoft Access and Microsoft Excel you would think would be hand in hand... but the problem has persisted for years that it has always been problematic getting the correct data types between Access and Excel columns when Importing and Exporting to and from Access to Excel and vice versa.
Excel doesn’t support data types which makes it very hard for Access to recognize data types from Excel when importing.
When using the wizard to import data from Excel, you can determine the column types and save the steps – this could be a workaround to mitigate the issue.
it is time to review the entire Access/Excel connectivity. There are numerous suggestions that refer to this. In particular, to the translation of data types.
Now that the patent preventing MS Access from updating linked spreadsheet has expired it should be time to re-implement and review this feature along with importing and exporting.
John Partyka commented
Yes! I work in a data research environment where uploading and downloading to/from Access and Excel is a very common procedure. It amazes me how little thought Microsoft put into the interface between these 2 great products. Treating an Excel worksheet as a shared table is an excellent idea but it has too many limitations to use frequently. What's needed is a way to define a flexible template form that identifies where the shared data elements are in Excel, much like we do in Access forms.