Results 1 to 5 of 5
  1. #1
    dubay013 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8

    Best way to update multiple existing tables in Access with data from multiple excel tables?

    Hello,


    I work in natural resources and manage survey data that we collect throughout our entire state. We have a rather large and complicated Access database and are making some changes to our data collections methods (switching over to iPads) that will affect how we enter the data into our existing access database. I basically now get an excel workbook with 2 tables containing all the data I would usually manually enter into our access database. This data is formatted and ready to be imported into our database. I get one of these workbooks per site sampled, so can get anywhere from 50-250 of these excel workbooks per season. These excel workbooks, with their 2 tables in each, contains data that I need to link/append/import into 3 existing tables in access.

    I'm wondering what the best strategy and most efficient way of doing this would be. I've researched creating a macro, but then I see forms in excel that I could create to have them upload/link/append to the access tables. If there is a way to link an existing spreadsheet to an existing table I could combine all the data and just keep copying and pasting it. It seems like there is a lot of options ranging in difficulty that I could choose from to import large datasets into our existing access tables. I'm just looking for some advice on what is the best & most efficient way to accomplish this to get me started.

    Thanks a bunch!
    Jenny

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    save the workbook as to a generic name, Data2Import.xls
    link the 2 excel sheets as external tables
    build 2 queries ...1 to import each sheet.
    put both queries into a macro.

    then the steps will be:
    1. save new workbook and overwrite Data2Import.xls
    2. run macro.
    done.

  3. #3
    dubay013 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8
    Doesn't sound too bad I can work in access but am not great at programming or using the tools (yet) that access provides if you can't tell. So just to clarify, I would basically just be updating the data2import workbook with my numerous workbooks & importing them? One access table only needs 2 fields from one of the work book. Can I still do that with a query?

  4. #4
    dubay013 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    8
    I did it! Thanks ranman256! The only error I can see occurring with this method is that it will allow me to upload datasets I might have already uploaded, but I kind of think that is the way our database is set up too.
    Thanks again!
    Jenny

  5. #5
    BogyOne is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    3
    If you place primary keys on the values you are importing then you should get import error (duplicate unique values) instead duplicated values.

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

Similar Threads

  1. Update Existing Access Tables with Data from Excel
    By ChelseaC in forum Import/Export Data
    Replies: 7
    Last Post: 08-10-2014, 04:28 PM
  2. Replies: 1
    Last Post: 08-07-2014, 10:04 AM
  3. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  4. Import Excel Data to Multiple Access Tables
    By colby in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2011, 12:17 PM
  5. Replies: 4
    Last Post: 06-14-2011, 07:19 PM

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