CODEWITHSUNDEEP
excelvbams-access
John von No Man
John von No Man

Reputation: 3030

Create an excel spreadsheet that actively modifies an access database

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

So, what should I do? Can I accomplish this within Excel spreadsheets using macros? Can you make buttons in Excel that would say "create a new table," which would (run a VBScript to) create a new database table to be associated with each project, and then allow you to format it? Should I not bother with Excel at all and basically write a Visual Basic program? I'm familiar with general programming and databases, but I am fairly new to Excel, Access, and Visual Basic. If you could point me in the right direction--to tutorials, examples, advice, general concepts, etc--it would be much appreciated.

Upvotes: 0

Views: 1245

Answers (2)

JeffO
JeffO

Reputation: 8043

For a person entering data, Access is built so they don't need to understand relational data at all. If you let them enter data in Excel, you will have to excessively code it to give you the same control in Access or you run the risk of letting them free form data to the point you won't be able to import it back into Access.

Unless there are very complex calculations and a need for the user to 'tweak' the report layout, give them a data entry form.

Beware of the "I'm just so use to doing it in Excel I don't want to relearn it in Access" notion. The data entry can be made very intuitive and may save them time in the long-run.

Seems like there is an owner/manager who understands Excel and wants the ability to update it without you if needed.

Upvotes: 0

unitario
unitario

Reputation: 6535

Excel is essentially for analyzing data, while Access is essentially for storing and processing relational data. Now, having said that, what you are trying to do is probably possible but it is really not taking advantage of the features the software where optimized for.

Furthermore, adding "tables to be associated with each project" does not seem as the "relational way of doing it", like a complex solution for a simple problem.

Perhaps you should consider some alternatives:

  • If the amount of data is small and not very complex, would there really be any need for Access or could you just as well use Excel for data storage and data manipulation?
  • Depending on how the data is structured, perhaps you can create a view or stored procedure in Access and used it as an linked table in Excel?
  • Perhaps you can develop the set of forms you need in Access and turn it to an stand-alone application (no need for Access installed on the client's computers)

Upvotes: 1

Related Questions