MS Access: Overcoming Data Type Issues When Using DoCmd.TransferSpreadsheet

If you are using DoCmd.TransferSpreadsheet to link to a file, you may encounter problems with the data types that Access 'guesses' once it has linked the file as an attached table.

Look at the example below:
When this file is linked to Access, the SedolCode column is considered to be a number, and as a consequence there are #Num errors because it cannot process 'B051Y83' as a number:
The registry folder My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel contains a value called TypeGuessRows. This indicates how many rows that Access will use in a linked file to determine a data type for a column:
As it is unlikely that you will be changing registry settings on a user's PC to overcome this issue, the alternative is to insert eight (or enough to match the registry setting) rows of dummy values that match the data type that you need:
Now when you link the file, Access will use the datatype of the dummy values you have inserted:
You can then either delete the 'dummy' records or ensure that your queries exclude any 'dummy' values.