Microsoft Access - Docmd.Transferspreadsheet acImport fine in 2007; in 2010, not so much

Greetings Eggheads and Nullskulls:

I've missed you.  Today's question involves running a 2007 accdb on 2010.  Only one issue was noticed, to wit:

The accepted process for importing xlsx data is via the docmd.transferspreadsheet routine.  When this is run on 2010, a certain column of a very large spreadsheet is present, but its values do not come in, as they did in 2007.  I noticed that when I manually imported the spreadsheet through the menu commands, there was no malfunction, all was well.  And the datatypes on the manually imported sheet match those on the table that receives the programatically imported one.

I think I've traced this problem to the elusive (but no less infamous) TypeGuessRows issue ( http://support.microsoft.com/kb/189897; http://support.microsoft.com/kb/281517).  That is, if no data is seen in the first 8 (to 16, depending on TypeGuessRows value) rows of the spreadsheet, apparently Access/Excel/Office/Bill-Melinda Gates decrees that there is no data, and therefore imports no data.  Or perhaps it renders the column in some datatype that Access wasn't expecting in that field, and, while not wanting to bother you with any indication that will do so, refuses to import it.

The only references I've been able to find to this (see above kb's) address truncating data that is > 255 characters, and that doesn't quite satisfy the powers-that-be as an explanation.  After all, it isn't cells that are being truncated, it's an entire column being ignored. Furthermore, they wish to know why, if this is a bug in 2010, it wasn't a bug in 2007.

My sense is that this is a consequence of the seemingly mysterious choices something in Office makes when it checks TypeGuessRows.  As this is clearly by design, what explanation do I have if -- all other things being equal, as they are -- this did not happen on 2007?  Was it a bug on 2007 that it did NOT exclude data?

Finally, as to fixes, the obvious is (according to MS) change TypeGuessRows to 0 in which case it checks up to 16,000+/- rows, which should be sufficient (assuming it stops as soon as it finds data).  However, tweaking registry settings is a little iffy around here, what with n layers of administrators and persmissions-givers/takers.  As linking the spreadsheet in question seems to recognize all the data like in the old days, that's first on my (very short) list of possible work-arounds.

Any and all constructive feedback or just plain commiseration would be welcome.

Rock on,

cd'c


ps: can't post links?

I don't know the answer to your specific question.  I don't use Access to export data into Excel and am not familiar with the docmd.transferspreadsheet routine.

That said, I would look at alternatives that don't involve these import/export tools from Microsoft and work up my own code/macro to import the data.  Office is an enormously complex software family and Microsoft is constantly screwing with it.  You can almost guarantee version to version that you are going to run into issues with stuff like this.

Your own code is going to let you devise your own auditing schemes and what to do when data violates it.  For example, you could create an Access table on the fly with all of its columns supporting large text blocks.  Then, iterate through the rows looking for violations that would exist in your production table.  Present those to the user during the import process and let them fix any issues, and then ultimately let the corrected data in during the import.

A lot more work to be sure but the user experience would be better and the reliability of your app would be much higher.

Agree with your premise completely.

Given the constraints of time and the current evironment, our fix was to save our spreadsheeets as csv's and then import those via the docmd.transfertext operation specifying "import specs", which were built via the wizard in Access.  I don't see any way to insert the data into Access from Excel without using those routines. 

This fix does work.  Import specs can be manipulated programatically for those who like a challenge.

Found something I should have known about all along: importing .csv with SQL.  Credit where it's due: http://www.utteraccess.com/forum/index.php?s=&showtopic=1964676&view=findpost&p=2099037

Turn spreadsheet into .csv.  Import via SQL. No import specs (which I was reluctantly having to become something of an apprentice on). No muss, no fuss -- well, a little fuss, but no data loss. A LOT less code.

Seems to be one step further out of the miry swamp of Microsoft's "my-way-or-the-highway" import functions which are all designed to make inter-Office development "seamless"!