Results 1 to 5 of 5
  1. #1
    bharatmvs is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    16

    Import Excel into a table, overwrite the data of the fields imported from Excel in Table

    HI,

    I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.

    So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.

    E.g:

    Say I have 2 records in my access:

    ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
    1 Fab1 193 NY Bug OC DC MC
    2 Fab2 641 DRS Error AC KC FC

    Now when I am importing data from Excel with the following records:



    Fab_name Issue_ID Location Remarks
    Fab1 193 NY - GRM Solved
    Fab2 641 DRS - SGP Forwarded
    So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.

    And I need this to be written as a macro, so that user just has to choose the file, which he has to import.

    Thank You,
    Bharat

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,360
    Can you establish a link to the Excel sheet? Then can use the link like a table (except cannot edit the data). Use the link in an UPDATE query. Otherwise, will need VBA code. Review http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bharatmvs is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    16
    HI,

    Thank you for the response. I cant use the link, because the files keeps changing and yes, I will have to change data in my Access as well. I couldn't find any code based on update in the link about VBA. I can find only codes for adding New Data into a specific table.

    Thank You,
    Bharat

  4. #4
    bharatmvs is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    16
    Hey, I have used update query. It satisfies my need. However I am struck at another point. How should I write condition for Yes/No field in criteria.

    and how to run update query using macro? Do.cmd.openquery is just opening the Update query, but not running it
    Last edited by bharatmvs; 03-18-2015 at 03:28 AM. Reason: Wanted to add more query

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,360
    I don't use macros, only VBA.

    Condition for Yes/No field would be set to either True or False.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  2. Replies: 4
    Last Post: 11-27-2014, 10:20 AM
  3. Replies: 1
    Last Post: 06-11-2014, 11:38 AM
  4. Access Import from Excel Overwrite
    By redpanda in forum Programming
    Replies: 8
    Last Post: 07-10-2011, 09:18 PM
  5. Linking Access Table with already imported External Data (Excel)
    By izzarshah in forum Import/Export Data
    Replies: 1
    Last Post: 07-29-2010, 09:40 AM

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