IME, importing into a temp table is generally the best approach. This lets
you check dates (Access does not always recocognise correctly), handle
currencies, sort out records that contain "NIL" in what is supposed to be a
numeric field, or "N/A" in other fields, check that data is present in
required fields, check that foreign key values match, or append data to the
lookup table for those records that need it and replace the values with the
key, etc, etc.
But that does not account for your experience that a numeric overflow occurs
only with an attached table. Have not experienced that. If this is writing
to a new table, Access guesses the field type based on the first few rows,
so the overflow can depend on the actual data, i.e. if the first several
rows appear okay and Access decides to use a small data type, then a few
dozen rows down a value is too large. Depending on what the data source is,
you may be able to define an import spec to indicate how to import.
Alternatively, you could import into an existing table.
Not sure that deals with your question well, but hope it provides a useful
lead.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Robert McEuen" <UN**********@yahoo.com> wrote in message
news:fb**************************@posting.google.c om...
Using Access 97, Windows XP
I'm receiving a Numeric Field Overflow error during text import that I
did not receive before I split my database. Another thread I found
suggested that the cause of this error might be due to columns on the
text file being in a different order than fields in the destination
table, but they're the same in my case.
I'm thinking a workaround might be to import to a temp table in the
front-end, run an append query from the temp table to the back-end
destination table, then delete the front-end temp. However, this
application performs 23 different imports, and I'd hate to have to set
that process up for each.
Does anybody know of a cause/fix for this problem? If a workaround is
required, is there an easier way than what I've listed above?
Any help is much appreciated. Thanks.
