Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    67

    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
    linked.
    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
    it linked.

    Any clues?

    Thanks in advance,
    David

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    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:

    Code:
    INSERT INTO MyDestTable (ColLongData)
    SELECT CLng(MyCSVCol)
    FROM MyLinkedCSVFile
    ???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2004
    Posts
    67
    Thanks for your response.

    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.

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Datatype

    Have you tried storing the datatype as a long rather than a double, currency or string?

    The different datatypes over a network is a rather peculiar one thogh. I've never heard of that.

  5. #5
    Join Date
    Jul 2004
    Posts
    67
    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.

    Thanks in advance,
    David

  6. #6
    Join Date
    Jul 2006
    Posts
    1
    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 ....
    any suggestion..please

    thanx...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •