TypeGuessRows setting

[Jeanette Cunningham]

Hi Everyone,
I am trying to import from Excel using Jamie Collins' guide to registry
settings for TypeGuessRows.
I have found that I can change the setting to a value between 1 and 8
(haven't tried any higher values)
The strange thing is that as soon as I use the import wizard to import an
excel worksheet, my setting for TypeGuessRows changes to 25.
This has happened several times. I assumed from Jamie's post that changing
that registry setting was a permanent change - does anyone know why the
setting would
automatically change to 25?

Jeanette Cunningham

 

[Ken Snell \(MVP\)]

If you set it to 0, Jet will look at all rows before it does the import.
I've never had a problem with it being reset by ACCESS.

 

[Jeanette Cunningham]

Ken,
2 weeks ago, it was set at 8. Without any intervention by me I found that
imports of empty cells in a numbers column suddenly behaved differently.
Investigation showed that the new behavior was due to the use of the import
wizard resetting it to 25.
I am curious if anyone else has found that it resets to 25 when they use the
import wizard at this point in time. A2003 SP3 and latest hotfix.

Jeanette Cunningham

 

[Ken Snell \(MVP\)]

An interesting result. I have not installed SP3 for Office 2003, so I cannot
provide a test for your setup (I still don't like all those bugs introduced
by SP3). I've not heard of this situation from others, but I'll ask around.

 

[Jeanette Cunningham]

Thanks Ken.

Jeanette Cunningham

An interesting result. I have not installed SP3 for Office 2003, so I
cannot provide a test for your setup (I still don't like all those bugs
introduced by SP3). I've not heard of this situation from others, but I'll
ask around.

 

[Dale Fye]

Jeanette,

Do you have the URL for "Jamie Collins' guide to registry settings"?

Dale

 

[Douglas J. Steele]

I suspect she's talking about
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

 

[Jeanette Cunningham]

Hi,
yes I am talking about the link Douglas posted here.
I am very surprised that access always sets TypeGuessRows to 25 whenever I
use the import wizard. Until 2 weeks ago, the TypeGuessRows was set at 8 ( I
had never even checked this setting before the last 2 weeks).

This has changed the behavior of the import process. Here is an example.
I have an excel file with one column of numbers - the first 51 cells are
empty except for the heading in cell A1.
When I import this into an existing table with field type number, Double,
there are no errors and the first 51 cells import as nulls into the table.
This is a good thing.
However, this is not how access was working 2 weeks ago. 2 weeks ago,
TypeGuessRows was set at 8 and an import as described above would fail with
an error. The only way to fix the error was to put a number in cellA2.

I am puzzled as to why TypeGuessRows has changed to 25. I now can set
TypeGuessRows to a number between 1 and 8, but as soon as I use the import
wizard, it reverts to 25. I don't understand why there is such a change in
the import process between now and 2 weeks ago.
If it is only on my computer that this happens, do you have any explanation
for this behavior?

Jeanette Cunningham

 

[Ken Snell \(MVP\)]

Jeanette -

None of the other MVPs have seen/experienced this problem so far.... sorry!

 

[Jeanette Cunningham]

Thanks for checking.

Jeanette Cunningham

Jeanette -

None of the other MVPs have seen/experienced this problem so far....
sorry!

 

[Faisal Noordeen]

Hi All,

I am also facing the same problem. I think this problem can easily be reproduced

 

[Flykiller666]

Hi,
i have the same pb
in each time i take wizard external link , the numbers change alone to 25.
can you help me
sorry for my english, but i'm french student...

 

[jannuser]

Hi Jeannette

No, you were not going crazy - I have the reset to 25 issue as well, on XP SP3 and fully patched Office 2003 SP3

 

[TomClem]

Ran into an issue where a SQL Server process that imports from Excel was not working correctly, due to MS Access import wizard having modified the TypeGuessRows value to 19. To make sure that the TypeGuessRows = 0 when our import process runs I created a batch file the sets the value to 0. We kick off the batch file, then proceed with the import and all goes well.
Text of batch file:
------------------------
@echo off

Reg Add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel" /V TypeGuessRows /t REG_DWORD /d 0 /f
------------------------

Hope this may help someone else too.