I am building a small application for a friend and they'd like to be able to use Excel as the front end. (the UI will basically be userforms in Excel). They have a bunch of data in Excel that they would like to be able to query but I do not want to use excel as a database as I don't think it is fit for that purpose and am considering using Access. [BTW, I know Access has its shortcomings but there is zero budget available and Access already on friend's PC]
To summarise, I am considering dumping a bunch of data into Access and then using Excel as a front end to query the database and display results in a userform style environment.
1. How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?
2. Do I pay a performance penalty (vs.using forms in Access as the UI)?
3. Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?
4. Any other things I should be aware of?
I have strong Excel VBA skills and think I can overcome Access VBA quite quickly but never really done Excel / Access link before. I could shoehorn the data into Excel and use as a quasi-database but that just seems more pain than it is worth (and not a robust long term solution)
Any advice appreciated.
I'd like to know how this discussion turns out as well. I have a similar project that I have made for teachers (it's a gradebook excel app). The reason I chose Excel is because there are a lot of calculated fields that has to be displayed and the spreadsheet UI is just the best thing there is when it comes to entering/viewing grades of students at the same time. However, maintaining the raw data is a nightmare. I'm thinking of porting the backend to Access while the frontend is still in Excel. I think for my purpose, this is the best setup unless there is an easy way to code a spreadsheet UI i
以上就是Using Excel as front end to Access database (with VBA)的详细内容，更多请关注web前端其它相关文章！