Import from Linked Spread Sheet In Access

[Prince]

a) I linked Excel Sheet in my data base and see "#Num!" in some columns in
linked sheet but don't see in actual sheet.
b) I am uwing Text filed in Import table with 255 length
c) I write codes which are doing good except those rows which have "#Num!"

I am getting runtime error "3349" numeric field overflow

can anyone solve this problem???

thanks in advance.

 

[Ken Snell \(MVP\)]

The error that you're seeing is because Jet (ACCESS) sees only number values
in the first 8 - 25 rows of data in the EXCEL sheet, even though you have
formatted the EXCEL column as Text. What ACCESS and Jet are doing is
assuming that the "text" data actually are numeric data, and thus all your
non-numeric text strings are "not matching" to a numeric data type. This
causes the "#Num!" error.

One way to "fix" this problem is to insert a ' (apostrophe) character at the
beginning
of each cell's value for that column in the EXCEL file. That should let Jet
(ACCESS) treat that column's values as text and not number.

Or insert a dummy row of data as the first row, with nonnumeric characters
in the cell in that column. That should let Jet (ACCESS) treat that column's
values as text and not number.

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/