Integrating Excel forms with Access

[slothario]

I'm sure this question has been asked and answered repeatedly elsewhere, but after a bit of searching around the internet I haven't been able to find a satisfactory answer yet.



My client wants to store basic relational data in Access. So far, so good. However, ideally, he'd like for me to create an Excel spreadsheet that would allow users to create and modify data types without having to work with Access software or know about databases. To be more specific, he wants a single spreadsheet that would let people manage data for several different "projects." Each project would have basic attributes and other related data such as employees working on it, numbered to do items with associated data, etc. I've worked with databases before and it's a neat, textbook example of a relational database. I have a model for the data already, and making an Access form to fill it in would be straightforward.

However, here's the thing: he wants creating new attributes and tables completely intuitive within the Excel spreasheet--as easy as clicking an "add student" button or even add a new category of data. For instance, in the future, he may add a list of contractors working on the project, and it would be nice to be able to have a button that would allow you to essentially create that new table. There won't be a great amount of data, though, and I'm not sure if referential integrity and normalization is crucial. For instance, the list of contractors he creates wouldn't need to be perfectly linked up so that each company only appears once in the database.

How much is this possible under Acccess and Excel? If you could point me in the right direction--to tutorials, examples, advice, general concepts, etc--it would be much appreciated.

[June7]

Create a new table for a list of contractors? That sounds like non-normalized data. Adding contractors should not require creating a new table, should just be new records in Contractors table.

Allowing users to modify data structure is very dangerous. Will they then be allowed to modify queries, forms, reports, code to handle the revised data? Users should just be able to enter and output data, not revise database structure.

Some table properties cannot be set programmatically. I have a procedure to create table and it has never been used for the very rare occassion a new table has been needed. I prefer to do it manually.