Numeric Overflow Error

[ttp]

I have created a macro that uses the TransferSpreadsheet action to link to an
Excel spreadsheet. The next action is an OpenQuery which uses the Excel
spreadsheet in a make table query. When I run the macro, I receive a numeric
overflow error (not all times that I run the query). Can someone please
assistance in understanding what this error is and how to correct it?

 

[Arvin Meyer [MVP]]

An overflow is usually caused by trying to put a long integer (> 32,767)in
to an integer field.

 

[Maarkr]

Are you linking to a date field or numeric field? If you use titles in your
ssheet columns, this can make Access think its a text field and can cause the
import formatting to be a problem. Sometimes it does, sometimes not. When u
link to a ssheet, define and name the data only portion of the ssheet
(Insert...Name...Define) and link to that name instead of the entire sheet.
That may help, depending on the data in your columns. You may also need to
clear out blanks in your query.

Search this Access site for more detailed info on how Excel data is imported
into Access if you're interested, but after messing with many of these,
column titles and mixed data in columns cause problems almost all of the
time. You should be able to link effectively without making a new table each
time.

 

[ttp]

I am still having an issue with this error. I can't seem to find what is
causing the error. When I have stepped through the macro to troubleshoot, I
am able to prompt the user to input a filename and the table is created in
Access using the TransferSpreadsheet action. The next step is to make a new
table using the linked table and querying for a specified field (SO field
using a like "gen CMU"). And this is where the error is occurring. Also,
the error does not occur every time I run the make query. I can't seem to
locate what could be causing the error. Is there another means to
troubleshoot or maybe another action I should use to revise the linked table
and make a new table. Any assistance will be appreciated.

 

[Arvin Meyer [MVP]]

The fact that it doesn't always happen is a pretty sure sign that you have
too small a data type created in your table. You need to fix the table
structure to handle a larger data type. You could also be putting to large
or small date in, or trying to fit more than 255 characters in a text field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com