I have begun researching to what extent we can use Excel as a complete DB application front end. I would love to hear from anyone else doing the same.
In many companies we work with, Excel is a big percentage of their user interfaces and software tools. However, those are all limited by what Excel can do computationally itself. Slapping an OLAP-supporting DB behind Excel could change that dramatically.
Anyone? Or am I charting a path where nobody wants to go?
i could say that we can use excel as front-end for any dbms and i am using this technique for a powerful reporting tools at my company. the only thing is that the retrieved data should be read-only coz it would probably be extremely difficult to update data from excel via ms query compared to using ms access or so.
in short, we can use excel to retrieve for all reporting needs, the report design will be very simple and most of all it will provide user-interactitve reports. for example, user can choose only a subset of data needed by some simple controls in excel (combo box, option button...) in addition, the data can be refresh periodically for the most updated.
Originally posted by qha_vn
the only thing is that the retrieved data should be read-only coz it would probably be extremely difficult to update data from excel via ms query compared to using ms access or so.
to manipulate data, i would use ms access
hope this helps
It does, but I am hoping to be able to use the Excel as an interactive interface... not just reporting. The style may be more like you see with web pages... you make multiple changes on a page and then hit a Submit button to get the changes back into the database... but functional, nonetheless.
Thus, I am curious why you think it "extremely difficult" to do so. Have you tried that at all? Any particular issues that will be problematic.
i meant 'extremely difficult' compared to getting the same thing with ms access. actually i havent thought of or had a need for such functionality within excel.
again i dont mean its impossible by saying that but it will not be easier then using ms access for data entry. (but it also depends on specific data manipulation need, isnt it? access may easier in this case but in other case, it may not)
I'm using Excel as Access database frontend in few projects. The reason is that the bureau where I'm working doesn't allow installing any applications to workstations, and Access is not normally installed to the workstation.
I'm using VBA, Excel forms and DAO 3.6. It's not very useful for large data mass or large amount of users, but as small office-level applications where only few records are updated/inserted at one time, it is sufficient (under 5 users, thousands of rows, LAN). If big INSERT queries are made, then the action slows down. Large SELECT queries are done quite quickly, 10000 rows takes about 1-2 seconds (from one table).
This solution allows many users to use the database at the same time (unlike Excel itself), and _no_ Access license is needed.
The hard part is to program all the functionality and SQL statements into VBA code. It requires some testing too, of course. If you're familiar to VB coding and SQL, it should be quite easy. Just be sure to turn on the DAO 3.6 objects from VB editor: Tools -> References -> DAO 3.6.
I think you are asking for trouble. You can probably do it, but budget for a lot of support down the road when the system needs to be modified or migrated. I don't use Access as a spreadsheet and I don't use Excel as a database.
The one thing I have found useful is creating pivot tables within Excel linked to database tables, queries or views. This is easy to implement, versatvile, and very functional. It is also, of course, read only.