Numeric Field Overflow

[steve.turner]

Hi all,

I'm hoping someone can stop me tearing (what's left of) my hair out
over this problem...

I have a linked Excel file from which I append the data to an empty
local Access table.
I have a macro which runs a delete query to empty the local table, then
the append query to update it with the new info from the spreadsheet.

I'm getting the error "Numeric Field Overflow" when running the
append query, it doesn't happen every time though.

I've found that I can make the append query run by going in to the
design view and making a change to something (anything), for example:
Re-selecting the "Append to" part of one of the fields (i.e. The
Supplier field updates to "Supplier" in the table and I'll
re-select "Supplier" from the drop down list, they query will then
run as it should)

Only one of the fields in the table is a number field and it is set to
Long Integer.

There are two currency fields and two date fields, everything else is
text.

I don't understand how the macro will run sometimes and, using the
exact same data, not run the next time?

Can a Numeric Field Overflow apply to a currency field or date?

My access knowledge is limited.
Access 2000, Win XP

Steve

[Mike Preston]

On 23 Jun 2005 02:20:57 -0700, st**********@gm ail.com wrote:

Hi all,

I'm hoping someone can stop me tearing (what's left of) my hair out
over this problem...

I have a linked Excel file from which I append the data to an empty
local Access table.
I have a macro which runs a delete query to empty the local table, then
the append query to update it with the new info from the spreadsheet.

I'm getting the error "Numeric Field Overflow" when running the
append query, it doesn't happen every time though.

I've found that I can make the append query run by going in to the
design view and making a change to something (anything), for example:
Re-selecting the "Append to" part of one of the fields (i.e. The
Supplier field updates to "Supplier" in the table and I'll
re-select "Supplier" from the drop down list, they query will then
run as it should)

Only one of the fields in the table is a number field and it is set to
Long Integer.

There are two currency fields and two date fields, everything else is
text.

I don't understand how the macro will run sometimes and, using the
exact same data, not run the next time?

Can a Numeric Field Overflow apply to a currency field or date?

Access optimizes stored queries as they are executed.

Obviously, Access's optimization is causing problems.

You have found the solution: re-save the query so that the
optimization is eliminated.

If doing it manually, before re-running your macro, is too burdensome
for you, you can create a routine in VBA that will copy and re-save
your query.

If you want that, respond in the positive and somebody with enough
time to rough out that solution will no doubt post it for you.

mike

[Steve T]

Thanks Mike, that's a great help.

I can't do it manually as it's a db that will be remote from me. If
anyone can help with the code I would appreciate it, in the mean-time
I'll have a crack at it myself as that's the best way to learn!

Thanks again.

Steve