MS Access: Overcoming Data Type Issues When Using DoCmd.TransferSpreadsheet
Published:
Browse All Articles > 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:
![Example data]()
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:
![Error importing data]()
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:
![Registry entry for TypeGuessRows]()
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:
![Dummy values to 'fool' TransferSpreadsheet]()
Now when you link the file, Access will use the datatype of the dummy values you have inserted:
![Data successfully imported]()
You can then either delete the 'dummy' records or ensure that your queries exclude any 'dummy' values.
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_MACHIN
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
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.