ODBC access error to an Excel spreadsheet verified through MSQRY32.EXE.

[Guest]

It appears that the ODBC driver for Excel (Microsoft
Excel Driver (*.xls)), will return null for ALL values of
a column if the first 8 rows for a particular column are
null. To reproduce:

- Create a spreadsheet with the first row containing
values A='col1' and B= 'col2'
- In rows 2-21, put values 1-20 in column A (col1)
- In rows 2-21, put values 1-20 in column B (col2)
- Start up MSQRY32.EXE and access that spreadsheet
- Verify that you get the following data:

col1 col2
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
....

- Now, in col2/B, delete the values in rows 2-9 (values 1-
8). Rows 10-21 should still contain values:

col1 col2
....
6
7
8
9 9
10 10
11 11
....

- Now, exit from MSQRY32.EXE and exit from Excel
(remember to save the changes)
- Start up MSQRY32.EXE, and access that spreadsheet again.
- ALL values from col2/B have are now null, not just the
values for rows 1-8.

What do I need to do to correct this?

 

[Michael Hopwood]

Try editing the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel\TypeGuessRows

The value of this key defaults to 8, set it to a higher value.