[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


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

Published on
12,923 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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics ā€“ known as key performance indicators (KPIs) ā€“ for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: ā€¦
Make it easier to see the current record on your Microsoft Access forms! To highlight the current record with a yellow background color, use Conditional Formatting, a control to keep track of the primary key value, a control to change color, and a lā€¦

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month