Odd Numeric OverFlow Error with MakeTable Query

[LarryP]

I have a maketable query that takes a linked Excel file and creates an
identical local table -- no manipulation of any kind going on, just "take
whatever is there in the Excel File and put it into a new table." Lately it
has been throwing a numeric overflow error occasionally, and I have no idea
why. (By coincidence, on two of the occasions we have gone into the Excel
file and made some changes to text data in one of the columns, but I can't
think of any reason that would cause the maketable process to suddenly
develop hiccups.) Any ideas?

 

[JamesDeckert]

Don't you get an error table created in Access which tells you which Excel
row is the problem?

James

 

[LarryP]

No, no error table is generated.

Don't you get an error table created in Access which tells you which Excel
row is the problem?

James

 

[JamesDeckert]

Do any rows import ok?
Do you make the changes in Excel, or are you editing the file some other way?

 

[LarryP]

Nope, the whole thing fails, and all I get is the generic "numeric overflow"
help.

 

[John Spencer]

It is possible that you have a text field that consists of only numbers and
the conversion process is typing the field as long integer based on the
first few fields. Then down further in the data the value of the text
number exceeds the size limit of an long integer and generates an overflow
error.

One way to deal with this problem is to define the table in advance - that
way you can specify field types.

Another way is to save the excel file as a text file. Then during the
import process you can define the field types.

Another way is to add a row to the top of the excel table that contains a
non-numeric value in every field. That way all fields should come in as
text. You can then
-- Delete the extraneous row
-- Convert the values as needed
(you could just add the row and put "A" in all the columns you wanted to be
sure to treat as text.)

Another way ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[LarryP]

Okay, thanks. I'll have a look at that.

It is possible that you have a text field that consists of only numbers and
the conversion process is typing the field as long integer based on the
first few fields. Then down further in the data the value of the text
number exceeds the size limit of an long integer and generates an overflow
error.

One way to deal with this problem is to define the table in advance - that
way you can specify field types.

Another way is to save the excel file as a text file. Then during the
import process you can define the field types.

Another way is to add a row to the top of the excel table that contains a
non-numeric value in every field. That way all fields should come in as
text. You can then
-- Delete the extraneous row
-- Convert the values as needed
(you could just add the row and put "A" in all the columns you wanted to be
sure to treat as text.)

Another way ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..