dandgard wrote ...
When access
imports a spreadsheet it reads the first record of the spreadsheet and
assigns data types based on the information it encounters.
It's not as simple as that! Standard answer number 4:
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. 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.
ImportMixedTypes='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
of 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 when '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 types exist? 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.
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).
--