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 dataWhen 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 dataThe 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 TypeGuessRowsAs 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' TransferSpreadsheetNow when you link the file, Access will use the datatype of the dummy values you have inserted:
Data successfully importedYou can then either delete the 'dummy' records or ensure that your queries exclude any 'dummy' values.

Comments (1)

Most Valuable Expert 2014

Myself, what I do, is create a dummy file of just a single row that has 'correct' data in each column.
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.

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.