Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    182
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update records from excel into access

    I would like to update records to the deceased section in access from a excel spreadsheet.
    I have a access dbase with patient #, name, and a deceased date. The Excel spreadsheet is setup the same way. Every month I get a excel deceased spreadsheet. How can I add the date of death from the excel spreadsheet to my access deceased date section? Please help I just manually typed in over 200 deceased dates. I'm using Access 2003

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 760 Times in 695 Posts
    Buckshot,

    What you want to do is:
    1. Link the Spreadsheet as an external data source.
    2. Write an Update Query that does the following:
      • Links the two tables {Access & Excel} on the Patient#
      • Set the link so it shows only matches between the two tables.
      • Update the Deceased date field in the Access table using the data from Excel.


    This is a general outline of what needs to be done. If you need more detailed help please post back.

    Update: Ok I've tested, using Excel 2007 but will be basically the same for all versions, and added graphics {make sure to click on graphics to see the whole thing} to show the entire process. Note the XLS file is the last graphic but I should have posted it first.
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-09-02 at 11:46. Reason: Add Graphics
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    182
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek thank you sooooo much--your the best

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 760 Times in 695 Posts
    Buckshot,

    You're welcome.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    New Lounger
    Join Date
    Jan 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG...Wonder if you can assist an old Aussie with this. I have an Access (2010) tblBookings with a DateCompleted field. In an Excel (2010) Template I have a linked tbl to the Access Dbase. I would like to enter a date into the Excel DateCompleted field in the Excel linked tbl and have it update the Access Date completed field. Is the problem I have related to the fact the Excel file is a "Template"......which is after completion saved under a different name ? Many Thanks

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 760 Times in 695 Posts
    Quote Originally Posted by BillBloxam View Post
    Is the problem I have related to the fact the Excel file is a "Template"......which is after completion saved under a different name ? Many Thanks
    Bill,

    Welcome to the Lounge as a new poster.

    Yes, if you use a different name each time you will have to change the file name in the update query. HTH

    BTW: It is proper Lounge etiquette to start a new thread with a new question and not add on to an existing one.

    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,494
    Thanks
    3
    Thanked 42 Times in 42 Posts
    Well, since this appears to have evolved into an old guys thread, I'll chime in (not sure who is older, but I've been around this forum for well over a decade). If you are working repeatedly with different Excel workbooks, linking to them may not be the best solution. You might instead create a VBA procedure that automated Excel from Access, and let's you use DAO or ADO to update the appropriate record. It's not a subject for the total novice to consider, but if you have some VBA skills, it opens up some additional options for move data between Excel and Access. If you want to explore the subject of automation, have a look at our Automation 101 tutorial. We use that technique to create some very complex reports for specific recipients in Excel, and then email them to the recipient (using automation of Outlook of course).
    Wendell

  9. #8
    New Lounger
    Join Date
    Jan 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG & Wendell,

    Many thanks. Having some success by putting data in a separate XL file and updating Access from there. The Access file presently only gets 1 field update from 1 XL file. This coul change of course if I ever get up to speed . Like the idea of the DAO or ADO update but as a novice will have to burn some midnight oil. Appreciate the tip on the Tutorial and will give it a try.
    Thanks again...Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •