none
Microsoft Access Can't Append All the Records in the Append Query

    Question

  •  

    I’m running an append query on and getting this error message:

    ‘Microsoft Access Can't Append All the Records in the Append Query’

    I checked the info here:

    http://support.microsoft.com/kb/303414

     

    In my table I have several fields of Data Type = Text; all are set to Required = No & Allow Zero Length = Yes.  I have several fields of Data Type = Number; all are set to Required = No.  I also have one field of Data Type = Yes/No.  I think one single record is causing this message to popup.  I have over 22,000 records in that table.  How can I determine which record, assuming it is not more than one record, is causing this problem?

     

    Thanks!!

    Friday, May 13, 2011 3:06 PM

Answers

  • As you can see, the main reason is type conversion failure within ONE record. I won't wonder if there is some sort of 'bad' data in one record. I can propose to come from the other side: try to use DBEngine(0)(0).Execute "your append query SQL code here", dbFailOnError

    Maybe you'll see more useful error description... but not the fact. :)

    Check twice all the records by filteribg, sorting, scrolling maybe you'll catch an incorrect one. However, how many records the query appends finally? According to the error, all the records should be added, but one of them should have Null value in some field.

    Also, could you post you SQL? 


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bruce Song Friday, May 27, 2011 11:11 AM
    Friday, May 13, 2011 10:08 PM

All replies

  • Hi,

    there is also an additional important info in this error message. Can you provide the full text?


    Andrey V Artemyev | Saint-Petersburg, Russia
    Friday, May 13, 2011 8:26 PM
  • Microsoft Office Access Can’t append all the records in the append query.

    Microsoft Office Access set 1 field(S) to Null due to a type conversion failure, and it didn’t add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

    Do you want to run the action query anyway?

    Yes, No, etc.

     

    So, I click ‘Yes’ and it runs, but I think one (or maybe more) record(s) is not correct.  How can I determine what is causing this?  It happened all of a sudden; I’ve never seen this until just recently.

    There are no Keys on this table.  Also, I tried to handle nulls, as I described initially, with fields of Data Type = Text set to Required = No & Allow Zero Length = Yes; fields of Data Type = Number set to Required = No; and the field of Data Type = Yes/No has a default value = zero.

     

    Any idea on how to manage this problem?

     

    • Marked as answer by Bruce Song Friday, May 27, 2011 11:11 AM
    • Unmarked as answer by Bruce Song Friday, May 27, 2011 11:12 AM
    Friday, May 13, 2011 9:55 PM
  • As you can see, the main reason is type conversion failure within ONE record. I won't wonder if there is some sort of 'bad' data in one record. I can propose to come from the other side: try to use DBEngine(0)(0).Execute "your append query SQL code here", dbFailOnError

    Maybe you'll see more useful error description... but not the fact. :)

    Check twice all the records by filteribg, sorting, scrolling maybe you'll catch an incorrect one. However, how many records the query appends finally? According to the error, all the records should be added, but one of them should have Null value in some field.

    Also, could you post you SQL? 


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bruce Song Friday, May 27, 2011 11:11 AM
    Friday, May 13, 2011 10:08 PM
  • If you have a copy of SQL Server (2005, 2008, or Denali CTP3), you should be able to use SSIS to run your query.  In the bottom of the dtsx package, there is a space to create connections... create one (source) that points to the Access database and the other (which will be your Destination, not the Source connection), you'll need to point to the table you are entering data into.

    In the top portion, add a Data Flow task and double click it.  Go to the toolbox again and add either an ole db source or ADO NET source and point (edit from the dropdown when you right click) using the Access database source as a connection.  Add another of the same type and edit and tie it to your SQL database table destination.

    When you execute, it will let you know which record number it stumbled on and you can view that record directly to discover what might be wrong.


    R, J

    Sunday, July 31, 2011 8:12 PM