Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    numeric field overflow (Access 2003)

    I have a database that links to spreadsheets as well as Oracle tables.

    I am getting a numeric field overflow error. I have checked the properties and I believe they are set properly.

    I am running an append query. The odd thing is that if I make a change in design view, it will work fine.
    The second time I run it - I receive the error message.
    If I open it back up in design view and do something as simple as typing a character in the criteria and backspacing it out - it will run fine.

    I read somewhere that Access does some sort of optimization and when you alter or make a change it loses the optimization until it re-saves thus allowing it to work right after a change.

    If I make a change, close the query (and save changes), and reopen the query it will produce the error.

    It seems that it will only work directly after a change.

    I read somewhere that you might want a macro that will cause a change right before the append query runs.

    Can anyone help me?

    Thanks
    Bill

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: numeric field overflow (Access 2003)

    It sounds to me like some of the data you are trying to append is too big for the field you are trying to put it in.

    Do you have numerical fields in the destination table? What data type are they? They will be number, but they might be integers or long integers or singles etc.

    Can you isolate which field causes the problem? What is the biggest number you are trying to put into this field?
    Regards
    John



  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: numeric field overflow (Access 2003)

    Bill,

    Three quick things to check.

    You may get an overflow error if
    1. You try appending a text field in a numeric field or vice versa (I cant remember) - basically wrong data type or
    2. You try appending a null value in a field that is required
    3. You try appending a null value to a table into a field that is part of a primary or foreign key (also a required field)
    Regards,

    Gary
    (It's been a while!)

  4. #4
    New Lounger
    Join Date
    Jun 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric field overflow (Access 2003)

    I have confirmed all of this but I will check it again.

    I really need to express that the query will run the first time.
    It is after Access does some internal optimization that error exists.

    Will it still run the first time if the field types are incorrect?

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: numeric field overflow (Access 2003)

    Bill,

    Let me take a step backwards. The query that you indicate runs the first time, is it an Access Query and is the data it is getting through an ODBC to Oracle, a .CSV file, tab delimeted, or data imported from Oracle to an access table or other means. Does all of the data the first time actually append to the table? (Check total counts imported against the query).

    I know you say it (the query) runs the first time but does it fail if you were to run the same data again? Are you able to see where in code the failure occurs?

    Sorry for all of the questions but I am trying to understand what is taking place. Are you sure you are not passing any null values that could cause the data to shift positions or be perceived as an incompatible data type?
    Regards,

    Gary
    (It's been a while!)

  6. #6
    New Lounger
    Join Date
    Jun 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric field overflow (Access 2003)

    Yes - the first time it runs all of the data appends to the table.

    It does import the corrent number of records.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: numeric field overflow (Access 2003)

    Bill,

    Is the query is launced via code, i.e, DoCmd.OpenQuery "Query Name" using a command button.

    If so, try putting in a Docmd.Close statement on the query. It seems after the query runs, it does not release from the table. Thus, your physical closing the query or modifying it forces the release.

    You also didn't indicate where the data is coming from. Is the data imported to an access table and you are running the append on the table or are you getting the data some other way? It may make a difference.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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