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

Published on
13,325 Points
2 Endorsements
Last Modified:
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.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free