Update an Access database (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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

Reply | Quote