Make table query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I have a linked table that is a link to an Excel file.
I' ve created a make table query that simply creates a table exactly like
that linked table. But when i run the table i get a "Numeric Field Overflow"
error. This doesn't happen if instead of running the query i preview the
query.
How can i solve this problem?

Thanks

Luis
 
I forgot to mention that the make table query creates a table base on the
linked one but with a certain criteria. I don't need all the records of the
original table.
 
Hello
I have a linked table that is a link to an Excel file.
I' ve created a make table query that simply creates a table exactly like
that linked table. But when i run the table i get a "Numeric Field Overflow"
error. This doesn't happen if instead of running the query i preview the
query.
How can i solve this problem?

Thanks

Luis
Click to expand...

I'd suggest creating the desired target table, empty, in the table
designer; then use an Append query to fill it. Since Excel does not
have strong data typing, Access must guess at the correct datatype for
fields, based on the first few rows of the spreadsheet; it can guess
wrong, for instance using an Integer field because the first few
numbers are small. If there's a much larger number down the sheet (or
if the "numbers" are actually text fields such as PartNumbers) you'll
get this error.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John Vinson said:
Access must guess at the correct datatype for
fields, based on the first few rows of the spreadsheet; it can guess
wrong, for instance using an Integer field because the first few
numbers are small. If there's a much larger number down the sheet (or
if the "numbers" are actually text fields such as PartNumbers) you'll
get this error.
Click to expand...

John,
Yes, I've read that article too:

"Numeric Field Overflow" error message occurs when you query a table
that is linked to Excel spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;en-us;815277

It appears that when querying an Excel table via a linked table, MS
Access tries to determine the data type itself and can arrive at the
answer of Integer, even though Excel's only numeric data type when in
'database' mode is Double (FLOAT). In going out on a limb in this way,
MS Access seems to trip itself up when it encounter a Double value
than cannot be cast as an integer. Not very clever.

On the other hand, when querying an Excel table *directly* (i.e. no
linked table), MS Access simple accepts the data type as determined by
Excel itself. Actually, Jet determines the Excel data type but it is
Jet on the *Excel* side ('Excel Jet') rather than Jet on the MS Access
side. For details on how 'Excel Jet' determines data type, see:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

So it raises the question, Why doesn't MS Access just go with the
'Excel Jet' data type when querying a linked table, rather than trying
to do it itself if it sometime leads to failure? Using the 'Excel Jet'
data type would mean it would work every time.

Jamie

--
 
Back
Top