Results 1 to 9 of 9
  1. #1
    Doreen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4

    Import table primary key violation

    i am new to access, when i import i have records deleted due to key violation, how can i get these deleted records to an error table so i know that the right record was deleted?

  2. #2
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481
    Are you importing into an existing table? If so, try importing into a different table name. If you need to define the fields, don't define any as autonumber or primary key.

  3. #3
    Doreen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4
    actually just begining a table...cannot go on until i get a report on what it is deleting due to duplicates, ie...sometimes CSR's enter a payment as $0, realize their mistake then reenter with actual payment.

  4. #4
    Access_Blaster is offline User
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2010
    Posts
    339
    Sometimes access will create a "import error" table, have you looked?

  5. #5
    Doreen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4
    I have, I have seen that in the past but not always and not on this one

  6. #6
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Your post suggested that you are importing a table, but later

    you suggested that you were just starting a table. However, to get a violation as you described, aren't you importing values into a table where at least you have defined a field as a primary key?
    If you are still stuck, try importing into a less restrictive table until you get it sorted out.

  7. #7
    Doreen is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    4
    Yes like I stated earlier my problem is that CSR's enter data twice, they forget to enter a $ paid then reenter the data with a $ amount. I did forget to state that the information on all of this is sent to me via excel which I import into this table, the primary key is date paid and work order date, even if they pay twice in one day I only give credit to the collection rep once.

  8. #8
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Clearer

    Since I doubt that you can eliminate the entry errors, importing to a Temp table would prevent destruction of data. Then you could run a query (or several queries) to present the duplicate records, delete the errors and update your permanent table. Perhaps you could develop a screening query that eliminates most, if not all, of the duplicates.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,331
    I agree with hertfordkc you need to work with the raw data, then you decide whch records to keep/omit/process...
    Seem to have a couple of choices
    - you could try to get cleaner data from Excel before moving to Access. or
    - you could import all the data into an Access table with no primary keys, and no unique indexes on fields.

    Then work out some queries (logic) to determine what is "wrong" with records from a business view --duplicate payments, $0 payment etc whatever you decide makes a record "wrong".
    Reconcile all of the wrong records, then build records to go to your "good" table.

    When access creates import errors it's usually because of wrong data types, text in numeric fields etc. It doesn't automatically know that a payment of $0 is incorrect.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-11-2011, 09:22 PM
  2. Replies: 18
    Last Post: 07-26-2011, 04:38 AM
  3. table relations & primary key
    By mwabbe in forum Access
    Replies: 9
    Last Post: 08-19-2010, 10:09 AM
  4. import the foreign key into the primary key
    By vCallNSPF in forum Forms
    Replies: 3
    Last Post: 01-14-2010, 06:51 PM
  5. append, Key violation
    By klapheck in forum Queries
    Replies: 0
    Last Post: 09-17-2009, 11:50 AM

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