Slightly lazy VBA programmers might enjoy using the less-than-cryptically-named docmd.transferspreadsheet command when trying to automatically move information from an Access database to a spreadsheet or vice versa. It allows you to work with spreadsheet ranges and so on with minimum faffing. It also doesn't work so great and can lead to "unexpected results" when importing from a spreadsheet.

The problem appears to be with data types. To transfer some Excel data, for example, into an Access table, Access clearly needs to know what sort of it needs to be dealing with to set up the correct field types. To do this, it examines the incoming data, but it seems it only examines the top X rows of it - X may be around 25ish?

Where this situation isn't useful is for instance if your first X-1 records are numbers and then your X+1st record involves a letter. By the time it comes to import this record it has already decided that the field should be only numeric. You don't get any errors or warnings, but rather the entry for that field is null, rather than whatever letter it should be.

Given you can't use import specifications with transferspreadsheet and it doesn't take hints from Excel's cell formats there's not a particularly nice 'n' obvious workaround. You could code your own, working, transferspreadsheet function, or alternatively it seems (in Excel anyway) if you programmatically append a single quote mark to the beginning of the first bit of data your import, that will persuade it to ready a field to accept both text and numbers. One way to do this for cell A1, assuming xlSheet is a Worksheet object, would be: