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

    Import from Excel to update table

    Hello,

    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
    20233631
    20233567
    20233567
    20233539
    20233539
    20233392


    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
    Pillic

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

    Code:
    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
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Pillic is offline Novice
    Windows 10 Access 2003
    Join Date
    Jan 2021
    Posts
    2
    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:

    Code:
    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
    Cheers

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