Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update an Access database (2000)

    I've been asked to look into the idea of updating an Access database (adding and editing records, rather than just querying) from within an Excel spreadsheet. Is this possible? Is it a sensible way to do this (I think probably not).
    Any views or ideas of the best way to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update an Access database (2000)

    I don't know if this is possible, but I don't think that it is a very good idea. Excel is not a database program, although it has some nice datbase-like capabilities.

    You *can* create a link in an Access database to a table in an Excel spreadsheet. You will then be able to edit the data in Excel and in Access. You might want to use a dynamic named range as the source of the link. This makes it easier to add new records to the table in Excel. Do a search for "dynamic range" in this forum and you'll get lots of information.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Update an Access database (2000)

    It's definitely a bad idea for routine maintenance of the data in a production situation, it would unnecessarily and substantially complicate things.

    The only situation I would consider using Excel as a source to Access is if you were adding a single table or dataset to an existing or newly created database on a one-time only basis, or if the database is and will remain a single table database (which never seems to happen).
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Update an Access database (2000)

    Possible, yes. Sensible, no, as has been mentioned. To do it would require a lot of coding and be unnecessarily complex. I'd suggest doing the whole thing in Access - you can always produce Excel exports and imports as necessary if that's an issue.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update an Access database (2000)

    Thanks for all the input - as suspected NO is the correct answer!!

Posting Permissions

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