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

Published on
12,766 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.
1 Comment
LVL 26

Expert Comment

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.

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month