Re: What is the best way to work with SQL SERVER data / tables?

"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in
news:Xns9BBFA99E4F85f99a49ed1d0c49c5bbb2@xxxxxxxxxxxxx:

A form in Access with a WHERE clause will request from SQL Server
exactly the same data that would be returned from a SQL Server view
with the exact same WHERE clause.

Open your form without returning any records (you can do this by
assigning a blank recordsource or by returning a non-editable single
record -- ask if you want details on how to do that), and then ask
the user which data they want to view. Then assign the recordsource
with the appropriate WHERE clause that restricts the dataset.

However, it may very well be that this recordsource will not be
editable, either. One way to avoid that problem is to use IN (SELECT
Table.* FROM Table) as criteria instead of a join.

Well, that's pretty stunning!

Who would have thought that opening a form with no records, asking the
user which data he/she wants, then assigning the record source with the
appropriate WHERE clause would obviate the need for SQL-Server Views? How
embarrassed the creators and developers of SQL-Server must feel; after
many years of development and progression through several versions, and
the investment of millions of dollars, David establishes that a simple,
though perhaps inconvenient routine, makes one of the great strengths of
their project, VIEWS, unnecessary, redundant, and, well, pointless!

I can?t think of any problems. Well, I suppose:
* One can?t index the record set returned by a dynamic sql statement but
one can index a View;
* Views are often based on complex SQL statements, even other VIEWS;
handling an 8000 character SQL statement in code may not be trivial;
* Security, Security, Security; I guess one couldn?t use Windows
Integrated Security (I think Windows Integrated Security is recommended?)
with David?s method else the user could just open a new MDB, connect to
SQL Server and presto, everything would be available to him/her without
the benefit of the application where he/she was supposed to be working;
so I guess it would be a login and password, hidden one assumes, in code
and of course, to effect the hiding we pretty much need an mde; well I
should have said a secured mde, else anyone could open the mde and mess
with the data;
* I suppose there?s simplicity; you know where you click in the record
source box and that pull down of all the tables, views and stored
procedures appears and you click on one and presto, the data base for
your form is done!

But I suppose these are inconsequential for an amateurish Access
database. Who cares if things are fast, secure, simple and efficient? My
major concern with just using a where clause is that the developer is not
encouraged to learn SQL-Server when he/she uses this kludge. Why should
anyone want to earn SQL-Server? Because learning is the ultimate good and
after you?ve learned something, you might actually put it to some use.

But David?s post shows that VIEWS are redundant and I expect MS will
remove them from the next version of MS-SQL Server so, enough said. I?m
trying to guess why he was able to have this brilliant ?Ah Hah? insight
when those of us who have been working intensively with SQL-Server just
accepted Views and their use meekly. Perhaps, it is because his view is
recent and fresh.

I may say how encouraged I am when opponents of ADPs and ADO propose DAO
and VBA methods which I have had a hand in pioneering in the previous
century, as alternatives. Good show!

I am posting MS?s comments about updateable Views; I don?t see David?s
suggestion there but perhaps it?s implicit in one of their suggestions;
if not I?m sure they?ll include it right away, but I?m puzzled by it as I
never use IN in any non-trivial way and all of the views that I want to
be updateable are updateable; maybe that?s just because I read what?s
below:

Updatable Views

You can modify the data of an underlying base table through a view, as
long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must
reference columns from only one base table.
The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in
any other way, such as through the following:
An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
A computation. The column cannot be computed from an expression that uses
other columns. Columns that are formed by using the set operators UNION,
UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and
are also not updatable.
The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.

The previous restrictions apply to any subqueries in the FROM clause of
the view, just as they apply to the view itself. Generally, the Database
Engine must be able to unambiguously trace modifications from the view
definition to one base table. For more information, see Modifying Data
Through a View.

If the previous restrictions prevent you from modifying data directly
through a view, consider the following options:
INSTEAD OF Triggers
INSTEAD OF triggers can be created on a view to make a view updatable.
The INSTEAD OF trigger is executed instead of the data modification
statement on which the trigger is defined. This trigger lets the user
specify the set of actions that must happen to process the data
modification statement. Therefore, if an INSTEAD OF trigger exists for a
view on a specific data modification statement (INSERT, UPDATE, or
DELETE), the corresponding view is updatable through that statement. For
more information about INSTEAD OF triggers, see Designing INSTEAD OF
Triggers.
Partitioned Views
If the view is a partitioned view, the view is updatable, subject to
certain restrictions. When it is needed, the Database Engine
distinguishes local partitioned views as the views in which all
participating tables and the view are on the same instance of SQL Server,
and distributed partitioned views as the views in which at least one of
the tables in the view resides on a different or remote server.
For more information about partitioned views, see Creating Partitioned
Views.

---

Dare I ask, "Do other db engines have Views or their equivalents?" Yes
for some? Their creators made the same mistake? Shocking!

--
lyle fairfield
.

Relevant Pages

  • Re: New to SQL server
    ... it is called triggers. ... ANYTHING THAT YOU CAN DO IN JET IS BABY TALK COMPARED TO WHAT WE CAN DO ... anything that you can do in JET that I can't do in SQL Server ... self-referencing FKs and inline constraints. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ORDER BY in VIEW not working
    ... an ORDER BY clause returned ordered results. ... queries against the view or function that return data and those queries ... SQL Server 2000, determine what the row order might be and then ensure ... Whenever possible please post enough code to reproduce your problem. ...
    (comp.databases.ms-sqlserver)
  • Re: Near Clause usage
    ... NEAR clause using CONTAINS? ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... get the exact words I m looking for within my NEAR clause: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Access Form connected to SQL table in Access Data Project (.adp file)
    ... simply bad design to open up a form to a HUGE table, ... You can use what is called the "where" clause. ... the form what records it should load. ... we are NOT even using sql server yet. ...
    (microsoft.public.access.conversion)
  • Re: need information for "Before Select" Trigger
    ... As i've added "Enforced Constraints" ... There are no BEFORE SELECT triggers. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)