Problem with DoCmd.TransferSpreadsheet acImport

[Subenemi]

Hi all, I am having a problem with the DoCmd.TransferSpreadsheet
acImport
When I import a spreadsheet from Excel to Access, there is a column
that can have both numbers and characters.This column, when it has
both values, for example, a 0 (zero) and an A, if there are more than
25 lines with numbers, will only import the numbers, even if the table
definition on Access for that column is a text itself. All other cells
from this column only, will be blank.
For example:

Column 1 Column 2 Column3
Test 0 X
Test 1 X
Test 2 X
Test 3 X
Test A X
Test B X
Test C X

After import using DoCmd.TransferSpreadsheet acImport....


Column 1 Column 2 Column3
Test 0 X
Test 1 X
Test 2 X
Test 3 X
Test X
Test X
Test X

Any idea what is going on here?

 

[Douglas J. Steele]

When importing, Access looks at a certain number of rows and guesses at what
the data type of the field should be. If subsequent rows happen not to match
that data type, they're not imported.

Check what Ken Snell has at
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr

 

[Subenemi]

Thanks a lot Douglas, that was very clarifying information. Is there
anyway to force the import to read everything as text?

 

[Subenemi]

PS.: I read the article and checked the link that is in there as well.
It will require a change on the registry. I will have multiple users
using this file, so it won't fit to change all machines where it will
run.

 

[Douglas J. Steele]

Ken suggests three different approaches to get around problems. I can't
think of any others.

Don't forget that if your users have update access to the registry, you
could give them a .reg file to run, or even put code in your application to
update the registry for them.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


PS.: I read the article and checked the link that is in there as well.
It will require a change on the registry. I will have multiple users
using this file, so it won't fit to change all machines where it will
run.