Hi Vanessa,
The default registry setting for the "TypeGuessRows" key is 8. Therefore, if
your first (8) rows of data contain less than 255 characters, the data type
will be Text, thus truncating the extra data. This key is found in the
following branch of the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
So, you can either make sure that one of the first (8) rows exceeds 255
characters, or you can use the RegEdit utility, as long as you are
comfortable editing the registry, to increase this default setting to a
larger value (I'm not sure what the maximum setting is, although I *think*
that a setting of 0 will force the scan of all rows). Also, I think it is
best to quit Access and restart it when you make a change to this registry
key (not positive this is required, but it certainly would not hurt).
I searched Access 2003 Help (Offline Help) for the word: registry
In the hit list, I clicked on "Customize driver settings", followed by
"Customize Microsoft Excel driver settings". Then navigate to the subkey
indicated above. Also, from the initial hit list, click on the topic that
reads "Customizing Windows Registry Settings for Microsoft Jet". Then click
on the link that reads "Initializing the Microsoft Excel Driver". This occurs
in the first bullet, where some of the text indicated below is clickable
hyperlinks (assuming your HTML Help file is working okay):
You can also edit the Windows Registry to specify the following:
Settings used for interaction with Microsoft Excel, Lotus, Paradox, and
dBASE databases. See Initializing the Paradox Database Driver , Initializing
the Microsoft Excel Driver , Initializing the Lotus Driver , and Initializing
the dBASE Database Driver.
I've been experimenting a bit with this setting, as I've been preparing this
note to you. It appears as if the maximum might be hexadecimal (19) = decimal
(25). The reason I say this is that when I have restarted the registry editor
a few times, a setting of 0 that I made that was automajically changed to
this setting *after* doing a linking operation (it remained the same setting
of 0 when I just closed Regedit and immediately re-opened it, without opening
Access).
Anyway, I am able to run the following query on a linked Excel table, and
see that all of the original data from the Employees table, which I had
exported to Excel to start with, is still present. Note: I also added (10)
test records, with much shorter text in the Notes field (10 characters), as
the first (10) rows:
SELECT Len([Notes]) AS Expr1
FROM EmployeesLinked;
Yields the following result:
Expr1
10
10
10
10
10
10
10
10
10
10
414
212
221
423
312
260
151
When I ran my test with the default setting of (8), indeed, I do see #Error.
Hope this helps!
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
