Results 1 to 3 of 3

"...unable to append all data to table" message

  1. #1
    Sarge, USMC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2010
    Posts
    37

    "...unable to append all data to table" message

    So I have a database here with multiple tables......and when I try to import data to 1 specific table from Excel, the thing always tells me



    "MS Access was unable to append all the data to the table"

    "Contents of fields in 0 record(s) were deleted, and 0 record(s) were lost due to key violations.

    I've been using this DB for years now to manage data, and this issue with this one table started occurring last year. I've been ignoring it, because it's not affecting the data, but I'd sure like to put an end to this, for the sake of whomever is going to take over when I go.

    There are similar tables for managing different data. All are setup the same, ie short text for all fields except dates and currency.

    Also, with the other tables now....not with this one....when I import data from Excel, I get blank records being created in the table after the import that I have to delete. That started last year too, and again, I've been ignoring it, but I'd sure like to get this worked out.

    I should say I'm a novice level user.

    Any assistance would be greatly appreciated.

  2. #2
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    178
    1. Before attempting to import data, link the Excel Table to Access.
    2. Create a Select Query on the Linked Table and Open it in Datasheet View.
    3. Run a quick manual check on all rows/columns for the #Error or absence of expected data.
    4. If found anything unusual then go back to Excel and check the corresponding cells/Column data type and make corrections, if any, required.
    5. After that do a recheck on the status of your corrections.
    6. Ensure that no blank rows in between.
    7. When you are sure that all the data records are in correct form then only attempt to import/Append into Access Table.
    http://www.msaccesstips.com (Learn MS-Access Tips and Tricks)
    Learn Advanced MS-Access Programming with examples and VBA.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    This can also be caused by the data being of the wrong type, yes? Then the issue may not be so obvious as 13 and "13" look the same but the 1st is numeric, the 2nd is text. If that's the issue, it's usually best to link to the spreadsheet then import from the link into a table where the fields have been properly designed according to the data types required. It's commonly known/thought that Access determines the data type based on the first 10 spreadsheet rows.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 12-18-2015, 02:04 PM
  2. Replies: 14
    Last Post: 06-29-2015, 06:29 PM
  3. Replies: 1
    Last Post: 01-02-2015, 05:04 PM
  4. Replies: 22
    Last Post: 10-10-2013, 12:47 PM
  5. Replies: 3
    Last Post: 03-28-2013, 12:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums