Results 1 to 4 of 4
  1. #1
    Pillic is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021

    Import from Excel to update table


    I became an "admin" (dont know why) of an long time used application based on Access2003. As I never worked with databases I would much appreciate help from experienced users.

    An excel file will be provided with that an update in 1 column should be performed. Now I am looking for the best approach.

    The excel file will look like this:
    CommercialID Comment1 Comment2 Comment3
    20233656 1 2 99
    20233656 5 99
    20233631 6 77
    20233631 77
    20233567 77
    20233567 77
    20233539 99
    20233539 99
    20233392 77

    The table itself:
    sortetech_nr sorte_eigtx1 sorte_eigtx2 sorte_eigtx3
    20233656 1 2
    20233656 5
    20233631 6

    In the excel file the column "Comment3" should update the table "sorte_eigtx3" no matter if that field is used or not based on Excelfile column "CommercialID" = table "sortetech_nr".

    Is it possible to update the table directly from the excel file?

    Does the Excel file needs to be imported in to a new table and then updated via sql?

    What would be the best way?

    Thanks a lot for having a look into this question

    Best Regards

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    databases store data in a different structure to excel. Excel 'tables' are short and wide', database tables are 'tall and narrow'. So just a warning, applying an excel structure to a database will lead to either something that only works with a lot of effort or at worst, won't work at all.

    with regards updating the db, do the following

    link to the excel file - you can use code (transferspreadsheet) to do this
    write a query to append the data from the linked table to it's final destination

    note: with the linked table, if another spreadsheet comes along, simply replace the one you originally linked to i.e. same name/location, you do not need to relink

    an alternative method would be to just use a query - syntax would be something like

    INSERT INTO myTable (fld1, fld2, etc)
    SELECT efld1, efld2, etc
    FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\docs\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes])  AS XL;

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Kelowna, BC, Canada
    Just to add to Ajax's reply, the code shown will only add new records to the Access table; if you need to update existing ones you need to create a second Update query in which you join the Access table to the linked Excel by the sortetech_nr --- CommercialID.

    If all data entry is being done in Excel then you can simply replace the Access table everytime by using a Delete query to empty it followed by an append to populate it.

    Vlad Cucinschi
    MS Access Developer

  4. #4
    Pillic is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Hi Ajax and Vlad,

    thanks for pointing me in to the right direction.

    Due to not recognized CommercialID in the excel file I exported the 2 columns, manipulated them and then imported them again.

    Afterwards I run the following query:

    UPDATE  sorteEN INNER JOIN newTable ON sorteEN.sortetech_nr = newTable.sortetech_nr
    SET sorteEN.sorte_eigtx3 = [newTable].[sorte_eigtx3];
    This performed the action I required.

    Thanks a lot

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

Similar Threads

  1. Replies: 5
    Last Post: 04-26-2016, 05:30 AM
  2. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  3. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  4. Import Excel Sheet to Update Tables
    By guillermoftw in forum Access
    Replies: 4
    Last Post: 01-13-2015, 09:23 AM
  5. Replies: 17
    Last Post: 10-27-2014, 04:15 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 - Senior Forums