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
contains a value called
. 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.
I link to that, so the link table is all good.
I then use FileSystemObject to replace the dummy file with the real one.
When Access goes to open the linked file, it's all good.