Basic Direction Required

[Guest]

I develop in .NET, typically using a 3 tier approach where I have client
machines connecting to SQL server. Anyways, I got pulled onto a project
where they want Access to connect to a SQL server so that the users may have
a great deal of control over the queries and reports they generate.

What I want is an Access Project/Database that allows the user to see, use,
and create queries using a read only connection to the database. I have no
FREAKIN' idea how this is done.

I created an Access Project and connected to a SQL server and all the
tables/queries showed up nicely; however, this allows users to write to the
SQL server and I don't want them doing that.

What should I be looking into to get a READ ONLY view of the database for
the purpose of creating reports/queries. Thoughts on how to accomplish this
- how to architect it would be appreciated.

Does a project do this or do I just create a database?
Good books?

 

[Douglas J Steele]

IIRC, you can make the queries read-only by making them Distinct. Try going
into the SQL, and change SELECT to SELECT DISTINCT.

 

[Albert D.Kallal]

Why not set the permissions on the sql server side???

You don't set user permissions in VB, or the ".net" side anyway, so this
begs the question as to why security is not handled on the server side?

Further, you likely should consider using sql server reporting services
here..

Further, I don't believe in "end user" systems where they try and write
quires.

I have NEVER seen such a system work well in any company environment anyway.

The best approach is to build some prompt screens.

After all, if you give 3-5 different options for a sales report, you covered
99% of the users request.

For most of my applications, after a few months of request, they stop. In
other words, for a given database system, the reports needed can be
designed, and that should be the end of it.

You can see some screen shots of report dialogs here:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

 

[Guest]

Why not set the permissions on the sql server side???

Answer: I can only get read only access to the SQL Server, so in a sense,
the permissions will be set server side. However, in an Access Project in
order for the user to create a query they have to have permission to create
the query on the server and this permission isn't possible.

Further, you likely should consider using sql server reporting services
here..

Answer: Alot of the frustration you feel in answering my question is exactly
the same frustration I now feel. The server is under the control of a
different department and they won't allow us to use SQL reporting.

Further, I don't believe in "end user" systems where they try and write
quires. I have NEVER seen such a system work well in any company environment anyway.

Answer: When I first had a sit down meeting on this project my very first
question was "Could I please build this as a .NET application and design all
your reports for you -- if you request a report I'll build it until you've
got all you need. The answer was a firm 'NO' because, apparently, their need
for reports changes on almost a daily basis. (Nonsense, I know -- but I'm
limited by others here).

So I agree with your points, unfortuantely, the powers that be aren't
allowing for the obvious answers.

Currently the users have local copies of Access Databases that they write
queries against - huge databases larger than what Access is made to handle.
What I'm trying to do is get to the point where they hit the SQL Server and
don't store data locally (for security, efficiency, and space).
Unfortunately, in Access Project if I allow them to create a query it tries
to write it to the server and store the query there which simply cannot be.

So what I need is a query/report generator that can hit SQL server without
storing data locally.

 

[Brendan Reynolds]

So what I need is a query/report generator that can hit SQL server without
storing data locally.

</quote>

Perhaps I'm missing something, but haven't you just described an MDB with
ODBC-linked tables?

 

[Albert D.Kallal]

Great answers!!!

We are much on the same viewpoint from a developer point of view.

It just seems your hands are tied here a bit!!

Ok, my suggestion is that you setup, and provide some nice "views" on the
sql server side.

(that is some of the complex one to "many" joins etc, should be built as a
view on the sql server side).

now, create a REGULAR mdb, and setup some linked tables to those views.

You can now let uses "have at it", and they can create new queries to their
hearts content...

To eliminate the need to setup a DSN on each pc, you can use the following
code:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

So, no need to use a adp...just use a regular mdb, and linked tables (and,
better, link tables to some views sql side).

 

[Guest]

That is the advice I was needing.
Thanks Gentlemen!

 

[Nikos Yannacopoulos]

For what it's worth, I had the same issue with users wanting "absolute
freedom" in building their own queries... while my users don't even
understand the concept of a query, let alone building one. What I did is
I created a "super-query" with some 50 fields in it (using around 15
joined tables), and provided a selection screen (form) on which the user
may choose which fields they want returned, and what sorting they want.
The code behind it reads the checkboxes one by one, and builds the SQL
expression accordingly, then executes the query (which is actually a
make-table one, putting results in a temp table in their local front
end). This allows the most Access-ignorant salesmen to analyze their
sales by customer, customer category, region, month, product group,
product, customer's wife's hair-color-this-week, you name it... in any
combination. Then, if they want, they can get it over to Excel and play
with it as they wish. Believe me, you've never seen such a happy
salesforce (reporting-wise).
Actually I have three basic "super-queries", a flat one, a vs.LY one and
a crosstab one for monthly figures.
My concern at first was performance, given the complexity of the
queries, the size of the tables and the humble A2K back end, but it
turned out to be OK (thanks to Tony Toews and his superb performance
FAQ), and improved further when I converted the FE to A2K3. I imagine it
would be even better with an SQL server BE, and my complex queries as
views on the server side.

Regards,
Nikos