Results 1 to 4 of 4
  1. #1
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47

    Providing a local table with new data from a linked table via. update query

    Hi there,

    Wondering if this is even possible - but can I achieve the following?



    1. Link excel sheet to Access db as a linked table (Sheet1)
    2. Have a local table that contains all of the fields of the linked table (Students)
    3. Run an update query that will pull all of the data changes from Sheet1 into that of Students

    Any assistance would be vastly appreciated!

    Cheers

    Boost

  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
    Yes to all three EXACTLY as you described...

    1. External Data ... Import & Link Excel ... browse to your Excel file then select Link to the data source and follow the steps indicated
    2. Create the local table using a make table query using the linked Excel table as the source. Create a PK field e.g. StudentID
    This will copy all data across to the new local table tblStudents
    3. When the data changes, use an update query to update tblStudents from linked Excel table
    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
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Boost,

    The big question is where do you edit your data. If only in Excel Colin's script will work great. But if you edit it in Excel (pre-update) and Access (post-update) and you are trying to only update the latest changes made in Excel it is not going to work because you will overwrite the Access changes with the Excel data (some fields will be accurate but some will not). Hopefully you can come up with some sort of SOP (Standard Office Procedure) where for example you only add new records in Excel and only edit existing ones in Access (or Excel) -consistency is the key...

    Cheers,
    Vlad

  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
    Gicu makes a good point.
    It doesn't matter what code you use to add the records in Access, you need to ensure the data is only edited in one place.
    As you are importing data to Access, make the forms used read only for the imported data.
    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. Replies: 3
    Last Post: 10-06-2016, 10:18 AM
  2. Replies: 3
    Last Post: 10-08-2015, 01:02 PM
  3. Replies: 2
    Last Post: 04-13-2015, 03:01 AM
  4. Replies: 3
    Last Post: 01-01-2015, 05:25 PM
  5. Replies: 0
    Last Post: 05-21-2012, 11:54 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