Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    18

    Unanswered: Update Fields in DB from Excel Cell values automatically?

    Wondering how I can update an Access 2007 database with an excel 2007spreadsheet?

    More details-
    Have a database with addresses and then a bunch of fields(prices) that go with each address.

    We get new price changes in an excel format and the list can be kinda big.
    Be nice to somehow update the PRICE in the database automatically BUT it has to update the right record with right price of course. the excel sheet may not be in the same order as the records are in the accesss DB.

    Hopefully that makes sense.


    THANK YOU

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about making the xls a linked sheet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2008
    Posts
    18
    Sounds interesting..and might be a good solution

    I've never actually Done that before though

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good time to give it a shot then!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2008
    Posts
    18
    I did create an access table and link it to an excel sheet and it did function BUT for what we want to do...
    that excel table is going to change with NEW records and current records being removed and order may change..
    AND we only want to update one field from the excel table to the access database and basically it Has to look at the address to make sure it updates the correct record.

    i'm almost thinking this is going to take some coding to get it right..don't know

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I'd say it's going to take some coding!

    You might want to consider giving the "database" a range name in Excel and then using VBA in Access to re-link the spreadsheet on startup or whenever is most appropriate.

    it Has to look at the address to make sure it updates the correct record
    You might want to also consider giving each record a unique identifier to make that easier.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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