This is a newsgroup post from OneDayWhen. He’s an expert (in my opinion) on External Data. The issue he discusses is that of mixed data types. If you have, for instance, both strings and numerics in one external data column, you can get unexpected results - like some of your data doesn’t show up.

This is a common problem in the newsgroups and this is the best post I’ve ever seen on the subject. As OneDayWhen says himself, it needs looking after. Therefore, I’m posting it here in its entirety for all time.

Here my notes on the subject: (look after them; as my granddad used to
say, ‘I won’t be around forever, there’ll be one day when you’ll have
to fend for yourself, sonny’):

The relevant registry keys (for Jet 4.0) are in:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

The ImportMixedTypes registry key is always read (whether it is
honored is discussed later). You can test this by changing the key to
ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the
error, ‘Invalid setting in Excel key of the Engines section of the
Windows Registry.’ The only valid values are:

ImportMixedTypes=Text
ImportMixedTypes=Majority Type

Data type is determined column by column. ‘Majority Type’ means a
certain number of rows (more on this later) in each column are scanned
and the data types are counted. Both a cell’s value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
There’s a bias in favor os numeric in the event of a tie. Rows from
any minority data types found that can’t be cast as the majority data
type will be returned with a null value.

For ImportMixedTypes=Text, the data type for the whole column will be:

Jet (MS Access UI): ‘Text’ data type
DDL: VARCHAR(255)
ADO: adWChar (’a null-terminated Unicode character string’)

Note that this is distinct from:

Jet (MS Access UI): ‘Memo’ data type
DDL: N/A
ADO: adLongVarWChar (’a long null-terminated Unicode string value’)

ImportMixedTypes=Text will curtail text at 255 characters as ‘Memo’ is
cast as ‘Text’. For a column to be recognized as ‘Memo’, majority type
must be detected, meaning the majority of rows detected must contain
256 or more characters.

But how many rows are scanned for each column before is decided that
mixed and/or what the majority type is? There is a second registry
Key, TypeGuessRows. This can be a value from 0-16 (decimal). A value
from 1 to 16 inclusive is the number of rows to scan. A value of zero
means all rows will be scanned.

There is one final twist. A setting of IMEX=1 in the connection
string’s extended property determines whether the ImportMixedTypes
value is honored. IMEX refers to IMport EXport mode. There are three
possible values. IMEX=0 and IMEX=2 result in ImportMixedTypes being
ignored and the default value of ‘Majority Types’ is used. IMEX=1 is
the only way to ensure ImportMixedTypes=Text is honored. The resulting
connection string might look like this:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\ db.xls;
Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′

Finally, although it is mentioned in MSDN articles that MAXSCANROWS
can be used in the extended properties of the connection string to
override the TypeGuessRows registry keys, this seems to be a fallacy.
Using MAXSCANROWS=0 in this way never does anything under any
circumstances. Put another way, is has just the same effect as putting
ONEDAYWHEN=0 in the extended properties, being none (not even an
error!). The same applied to ImportMixedTypes i.e. can’t be used in
the connection string to override the registry setting.

In summary, use TypeGuessRows to get Jet to detect whether a ‘mixed
types’ situation exists or use it to ‘trick’ Jet into detecting a
certaint data type as being the majority type. In the event of a
‘mixed types’ situation being detected, use ImportMixedTypes to tell
Jet to either use the majority type or coerce all values as ‘Text’
(max 255 characters).