Why smart software can be so stupid – the Microsoft Access example

This is one of my favorite examples of the wrong way to deliver smart software.

Microsoft Access 2003 (2007 too I think) tries to be smart when importing an Excel spreadsheet. Rather than look at Excel’s data types, it looks at the data in the first 25 rows. Then, based on the patterns it sees there it infers a data type … (emphases mine)

Import, export, and link data between Access and Excel - Access - Microsoft Office Online

Data type  By default, Access scans the first 25 rows to guess the data type of the column. If Access encounters values beyond the 25th row that are not compatible with the chosen data type, it will simply ignore those values and not import them.

… You cannot change the data type of the destination field during the import operation.

It’s the combination of oh-so-smart cleverness (infer the data type) and pure stupidity (no user override of the inferred type) that makes this such a priceless example.

The lessons?

First, be very conservative about making your software “smart”. In general, you’ll make it stupid.

Second, if you’re going to make your software smart, let the user override the clever code.

It’s not like Access is a consumer tool anyway.

I can’t measure what an amazing amount of pain this stupid design has caused me over the years. It even afflicts linking to a spreadsheet from Access.