Your "abstraction layer" is the right approach to take with Access. Create an MDB with the basic views required linked into it and distribute to the users. Allow them to create new queries and reports in their own MDB as required.

Now how you are going to stop them from running a Cartesian join on tables with a million records or more I m not quite sure.

How about this one? i-net Clear Reports (used to be called i-net Crystal-Clear) has a powerful ad-hoc reporting component that is made to be an easy-to-use thing for non-technical users. Your users won t have to know anything about reporting at all. They simply select the kind of report, the data et voila there is a report suiting the needs.

The data abstraction can be done easily by creating so called data-views which can be designed by e.g. your administration. There are various ways to access the ad hoc reporting GUI. We have a web GUI, a Java Applet or a standalone Java program.

• The end users will not need any training since the GUI is highly intuitive.
• The views can easily be build by drag and drop in addition to setting datatypes, formats and so on.
• All reports (depending on security settings) can be accessed via DAV our a report repository gui.
• The server supports different security settings on a per user or per group basis.
• The standalone report designer is free and fully functional.

Disclosure: Yep. I work for the company who built this.

Microsoft have a free tool for business and end users which called "Report Builder". It supports the full capabilities of SQL Server Reporting Services. The good thing it is provides a Microsoft Office look-like user interface.

You can download latest version "Report Builder 3.0" from here http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=6116

And for more information about MS Report Builder check this link http://technet.microsoft.com/en-us/library/dd207008.aspx

Attempting to teach "non-techie" people T-SQL to query a schema with 400+ tables probably isn t going to do well, unless they are limited to querying the views only, and the views hide all the ugly complexities of various joins, grouping etc.

Our company was in a similar situation where Access was used early on, and then we switched everyone over to use T-SQL and SSMS. IMO, this is the approach you d want to take.

Again though, the success of this will depend on the quality of your views, or better yet, reports you provide your end-users.

Randy

I would look more into something like Stonefieldquery.com that is designed for non developers to build reports. Not that the report writer or query builder in Access is bad, but may be too much. I think they also provide a way to centralize reports and queries where they can be shared. Multiple people are not going to be able to open a single access file and create a report (I think query building is OK.).

Most will use the drag and drop capability, but about 5-10%will come thing a need for SQL and then you can take advantage of the "teachable moment" and get them some training.

Cons for Access certainly would be cost; SSMS should be free assuming you re properly licensed for the SQL server.

Depending on the actual needs, some users might actually be better off with Crystal Reports (never thought I d say that), or Reporting Services.

you could create a series of sql server analysis cubes and have the users conenct to those using excel so that they can use excel s pivot tables.

Being a newbie at ad hoc reporting and doing the work myself, I used Izenda.com ad hoc reporting. It was very straight forward, and I could do it myself versus outsourcing.

Check SQLS*Plus - http://www.sqlsplus.com

I found SQLS*Plus to be a very effective command line SQL server reporting tool - this is a free tool (for personal use) and allows me to generate reports with the titles, headers, in HTML and CSV formats, format columns in custom masks, set report length, pagesize, etc. As I understand it is very similar to very well known Oracle SQL*Plus reporting tool