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.

to manipulate data, i would use ms access

hope this helps

thanks for your comments :)

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)

cheers

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.

JN

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.

blindman