Results 1 to 4 of 4
  1. #1
    mgw2786 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    1

    Help with Import Data from Excel Errors

    Hi,

    I am working on a class project that requires us to import data from an excel sheet made by the instructor into an already existing table in access. I keep receiving error messages that says "Microsoft access was unable to append all the data to the table." But then it says that 0 records were deleted and 0 records were lost due to key violations. What does that mean?

    Also when I try to just import the data into a new table it says the primary key cannot contain a null value. What does this mean?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the target table has a keyed field.
    you cannot load data from the excel file if this field is null. Every record must have this data in this field.
    This could explain the other error message.
    Either fill the field before import,
    or
    attached the excel file as a table and make a query to import the xl table and exclude the null records.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I have seen this kind of "bogus" errors when there are a bunch of blank rows under the data you are importing that are also trying to get imported. Many times, these rows have formatting in this, so Access is trying to import them as well.

    If you go into the Excel file, and hit CTRL-END, does it go down to the last line with data in it, or below that? If it goes below that, delete those blank lines between that and your last actual row of data, save your file, and try again. See if that makes those errors go away.

    Another way is to name the range with the data you want to import, then import the named range into Excel (instead of the whole sheet). That will ensure that Access is not trying to import those blanks rows too.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    But then it says that 0 records were deleted and 0 records were lost due to key violations. What does that mean?
    This is a composite error message (there may be more than one like this). The failure of the operation can result in deleted records, records dumped (lost) because the primary key you're trying to add is already in the target table, there are lock violations, rule validation violations, etc. The number of records affected is given for each problem type that is applicable to the error. The answers give are probably spot on with respect to the cause of the error.

    Here's a good explanation of the message type in case you encounter it again in another task.
    http://allenbrowne.com/casu-19.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2015, 03:56 PM
  2. Replies: 3
    Last Post: 01-05-2015, 02:21 PM
  3. Replies: 3
    Last Post: 05-01-2014, 02:27 PM
  4. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  5. Excel Import Errors-Type Conversion Failure
    By ejm2163 in forum Import/Export Data
    Replies: 5
    Last Post: 04-28-2013, 09:17 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
  •  
Other Forums: Microsoft Office Forums