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. Questions: 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. Alex
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