details). So is this functionality within the scope of the MS Access
development team (either developer <g>)? No. Will MS ever develop a
new version of Jet (i.e. Jet 5.0) or a new OLE DB provider for Jet
4.0? Very unlikely. Will a future MS Access circumvent the (stable)
Jet code and go directly to the Excel data? Even more unlikely.
As it is, the limitations can be overcome by changing the registry key
values. The only change I'd want is the ability for the registry
settings to be overridden by properties in the connection string (the
MS Access UI would then have to expose the currently hidden connection
string to the MS Access user). Possibly I'd also ask for the ability
to set MAXSCANROWS to any value between 1 and the maximum number rows
possible for an Excel sheet (the jump from 16 to infinity seems sudden
ACCESS ignores what the format of the EXCEL column is...choosing instead to
decide on its own ("don't confuse me with what the user says are the
facts!").
The facts are, Jet does look at the Excel cell's format as well as its
value. As proof, create an Excel workbook containing a single cell
formula
=38000
Change the cell format to (custom) dd mmm yyyy. Include the column in
a query e.g.
SELECT F1
FROM [Excel 8.0;HDR=No;C:\Tempo\db.xls;].[Sheet1$]
;
The value appears as 14 JAN 2004 (in local date format) and, using
ADO's
OpenSchema method, the column is show to have been determined as
adDate ('a date value'). Jet could only have deterined this to be a
date by looking at the cell format.
it looks at the first "x" number of rows (actual number of
rows depends upon how the EXCEL ODBC driver is configured on your PC -- for
me, its default number is 8 rows).
Not the ODBC driver, rather it's a registry setting for the version of
Jet you are using (again detailed in
http://www.dicks- blog.com/excel/2004/06/external_data_m.html).
Four possible ways around this problem.
You haven't included what IMO is the best way of solving the problem:
change the Jet registry key MAXSCANROWS from the default 8 to zero,
meaning all rows will be scanned to determine data type. There may be
a performance hit for large data sets or issues with admin rights to
amend the registry (e.g. at run time) but I find it preferable to
altering the data.
The first, and easier, is to insert as the first row of data a row that has
alphanumeric data in those columns.
Quick and dirty but not always possible to shift the whole data set
down one row e.g. due to dependent cell formulas, formatting, etc.
The second way is to insert ' characters at the front of the
value in each cell
Again, it may not be possible to put an apostrophe in front of a
numeric if it is being generated by a cell formula.
The third way is to create a temporary table (no primary key field) that has
the fields set for text format. Import the spreadsheet into that table, and
then use an append query to copy the data into your
permanent table.
I don't understand how this would work. You may format your temporary
Jet destination column as text but if the majority type for the Excel
column is determined to be numeric then the non-numeric values will
still be imported as null.
Fourth way (not likely that you should need to use this one!) is to use VBA
code to open the EXCEL file via Automation, and then read each row's data
one cell at a time and write the data into a recordset that is based on the
destination table.
Slow (to execute and to code) but often this *is* the
