Unanswered: Error 3349 (Numeric Field Overflow) Only If DB is Split
I'm getting the 3349 (Numeric Field Overflow) error when trying to insert new
records into a linked back-end table programmatically. The source is a select
statement from a linked text file (comma sep).
The craziness is two-fold:
1- If I import the destination table to make it local instead of linked, the
procedure works fine. But this is a split db, so the table really needs to be
2- I only get the error with certain source text files, not all.
So, I can successfully link text file A and select records from it to insert
into the linked table Z. And I can link text file B, but in order to select
records from text file B and insert them into table Z, I have to actually
import table Z (in other words, have a local version of it) rather than have
Have you tried explicitly converting the csv value to the data type of the destination since I believe Access has to make a "best guess" of the csv data type and tends to err high (as you would probably want...). E.g. if the destination field is a Long then:
INSERT INTO MyDestTable (ColLongData)
Yes, I tried that. I use CStr, CCur, and CDbl as the converters for the 3 data types that are involved.
I think the strangest thing is that it works fine if the destination table (the table I'm inserting INTO) is local, but errors if it is linked.
I came up with a work-around...which is to import the data into a temporary intermediate table that is local, then transfer them to the linked table. This seems to work. So the quirk only applies to data coming from SOME linked text files going into a LINKED table. Other text file sources will work going into a linked table, and all text files seem to work going into a local table.
Are asking if I tried changing the data type in the table? I haven't tried that.
Why would the INSERT process work for long but not the other types? And why does it work on a local table if the data type is wrong?
I worked around this problem by:
-creating an intermediate table by renaming and emptying the imported version of the destination table
-inserting records from the linked text file into the intermediate table
-inserting records from the intermediate table into the linked destination table
This seems to work 100% of the time. But I would still like to understand the problem with the original (and simpler) method.
I had the same problem....
I tried by creating an intermediate table....and importing the external excel file into the intermediate local table...
But when I copy the records (using select into from sql) from the local table to the destination table...Access converts that table to LOCAL table....
So I donot have those data into my linked table ....