Results 1 to 7 of 7
  1. #1
    KrishnaSiva is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4

    Post Refreshing Access database tables with new data from Excel source (Except Linked tables option)


    Hi Team,

    I have created a small database which is having 1 table. This table has imported from excel. when we import initially we have 3 rows / records.

    Later we added 2 more new records and few changes in already loaded records. So i am trying to updated the access table with the latest changes. tried with refresh all and refresh options but no luck, can anyone help me out with this.


    Note: I tried with linked table option its's working, but the problem is we are nit able to add new records in table when we do linked table.

    Thanks in advance.

    Siva

  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
    Slightly confused by your description.
    You have a table from Excel and another Access table which you want to update using the Excel dat.

    The Excel table should be LINKED not IMPORTED. That ensure the Excel data is always current.

    You have 2 possible approaches.
    1. Empty the existing Access table then run an append query to import all records
    2. Keep the existing records then run three queries in turn
    a) Append all unmatched records. That is those not already in the Access table
    b) Update existing records with any changes
    c) Delete all existing records not in the Excel file (optional)
    You can do these in any order

    Method 1 is simpler, but if using an autonumber ID field, it means the numbers will change each time.
    Method 2 doesn't change those values

    HTH
    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
    KrishnaSiva is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4
    Hi Colin,
    Sorry for that, here are the detailed steps about the requirement.

    1. I have created an empty database.
    2. I have MS excel document with1 sheet having 3 records, sheet name is “AB”.
    3. Imported the above excel “AB” sheet to the access database and named access table as “AB”. (So “AB” access table loaded with 3 records from excel).
    4. And later we added 2 more new records and made few changes in existing 3 records in MS excel “AB” sheet.
    5. Now am trying to do the “Full refresh” of the access table “AB”, to fetch the changes of “AB” sheet from MS excel. (so, after the successful refresh access table “AB” should have 5 records (Old = 3 records, new = 2 records and changes in source data for old 3 records)
    6. I tried to use “Refresh All” and “Refresh” options in excel to fetch the new records from excel, but no luck.


    Note: I tried with linked table option to update the access table with latest data, its's working fine. But the problem is we are not able to add new records in access table directly when we use linked table option to import excel data.
    Our requirement is we need to fetch the updated data from excel to the access table” AB” during the testing phase on daily basis. Once testing completes we are going to use the access “AB” table to update the changes or adding new records instead of excel.

    Thanks in advance.
    Siva

  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
    As previously described, either method will work for your setup.
    You just need to keep the required worksheet of your Excel file as a linked 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

  5. #5
    KrishnaSiva is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    4
    Hi Colin,

    As mentioned in "Note", i am able to fetch the updated changes to access if use "Linked Table".

    But concern is, we are not able to enter new records in "Access table" if we use linked table option.

    So, that is the reason i am looking for other work around to get the latest data to access except "Linked table".

    Regards,
    Siva

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I think we are at cross purposes.
    As already explained you can update Access tables from a linked Excel table.

    However, you cannot update the linked Excel table itself in Access.
    If you need to do that you must export the data to Excel or edit it directly in Excel.
    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

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    You must have 2 tables. An Access table, and a linked Excel table. And at some point (e.g. on opening the database, but you can also have a button on some form for this) a procedure must be called, which updates Access table from linked Excel table.

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

Similar Threads

  1. Replies: 11
    Last Post: 09-07-2017, 09:20 AM
  2. Replies: 2
    Last Post: 08-03-2017, 10:19 AM
  3. Replies: 1
    Last Post: 10-16-2014, 10:36 PM
  4. Linked tables and refreshing
    By offie in forum Import/Export Data
    Replies: 1
    Last Post: 06-20-2013, 01:42 PM
  5. Refreshing Linked Tables Hangs
    By blamb in forum Access
    Replies: 0
    Last Post: 12-05-2011, 12:36 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