Results 1 to 4 of 4
  1. #1
    roslynbuff is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    1

    Update Access Database with Excel File

    Hello,

    I am very new to Access (and databases in general) and would appreciate any advice anyone has! I have created an Access database to store information from the Learning Management System (LMS) that my company uses. The LMS creates data sets that I download as CSV files and save as Excel Workbook files.

    The LMS creates two different types of data sets. There is a full data set created every week, and then differential data sets created every day to capture the differences between the full data sets.

    I used a full data set to create a table in Access. Now I am trying to use a differential data set to update the table. So far, the only thing I can figure how to do is to append the differential file to the table. All this does is add in the data from the file. It does not use the data in the file to UPDATE the existing data in the table.

    For example: the table I'm currently working with stores data about course offerings. One of the fields is Recycle Date, which stores the date that a course was recycled. For one of the courses I am working with, when I used the full data set to create the table, there was no Recycle Date. In the differential file, there IS a Recycle Data for this course. I want to use the differential file to update the course offering record to add in the recycle date. Is this possible? When I use the append option, the data is just added to the table. I assume I need to identify a common field (key) for Access to use to know when a row in the Excel file matches a row in the Access table and thus needs to be updated, but I am not sure how to do that either.

    Any advice/help would be greatly appreciated!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use two queries.
    1. An UPDATE query to update all existing records
    2. An APPEND query to add new records.
    You can create a procedure to run each of these in turn.

    NOTE: There is something called an UPSERT or UPEND query which can combine both of the above in one query in certain situations.
    However, it is probably better to stick with two separate queries ...at least for now.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Further, if I may: you'll want the append query to not add existing records or certain field values again. IMO, easiest way is to ensure the target table has a unique index. This will not allow duplicate values to be appended in that index but you have to decide what constitutes a duplicate. At its simplest, the append query will balk when you try to add duplicate values, which may confuse or alarm users, but it will add those records that are not duplicates. A code procedure is the way to go to run the 2 queries if you want to suppress the warning about duplicates and just append those that are not duplicates.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks for adding that important point Micron.

    It needs to be an UNMATCHED APPEND query ....
    One method to ensure only new records are appended is:
    a) Create an unmatched select query with a LEFT JOIN between the source and destination tables and add add the filter that the target table PK field is Null
    b) Now change that to an append query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Excel Template File to Add/Update Access Backend Table
    By stildawn in forum Import/Export Data
    Replies: 3
    Last Post: 05-05-2020, 09:49 AM
  2. Replies: 3
    Last Post: 08-07-2019, 02:57 PM
  3. Replies: 1
    Last Post: 03-26-2016, 06:42 AM
  4. How to embed excel file into Microsoft access database
    By apoorv in forum Import/Export Data
    Replies: 7
    Last Post: 08-19-2015, 08:38 AM
  5. Replies: 3
    Last Post: 01-23-2013, 04:34 PM

Tags for this Thread

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