Results 1 to 4 of 4
  1. #1
    peres.br is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    2

    Update Access database from excel worksheet.

    Hello, guys!

    Here's the deal: I got an excel worksheet with informations about tickets of a system. I've already imported it to an Access table. Now I need to import periodically similar worksheets. I tried to do it by using the import wizard, as I did when I imported for the first time, but this new sheet contains some registries that are already in the table (with the same primary key) and need to be updated, so I got an error mesage saying that some registries were lost for key violations. That is, it does not get updated, it just ignores those registries. What's the best way to do it?



    It follows an worksheet as example:

    Exemplo Planilha.zip

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Import the new files to a "temporary table", which is basically like an empty shell of your production table (has all the same fields and properties, it just has no data).
    Then, after the data is imported, do the following:
    - Create a matching query between the temp table and your production table to locate all existing records. From this, create an Update Query to update existing records, if necessary
    - Create an unmatched query between the temp table and your production table to locate all new records. From this, create an Append Query to add new records to your production table.

    Once you have created this once, you should just be able to re-use this temporary table and two actions queries each time. Note that after each run, you will want to delete all the data out of your temp table so you have a fresh start next time.

  3. #3
    peres.br is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    2
    Perfect solution.

    Thanks!

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!
    Glad I was able to help!

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

Similar Threads

  1. Replies: 1
    Last Post: 03-26-2016, 06:42 AM
  2. Replies: 7
    Last Post: 09-24-2013, 04:53 AM
  3. Importing Excel - Update table field with the worksheet name
    By maggiemago3 in forum Import/Export Data
    Replies: 1
    Last Post: 08-22-2013, 04:51 PM
  4. Replies: 3
    Last Post: 08-22-2012, 06:28 AM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 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