SQL 2005 ORDER BY is ignored where form is based on a view in a AD

[Guest]

The problem: After upgrading my database to sql server 2005 from sql server
2000, the ORDER BY clause from SQL views is ignored in MS Access 2K and 2003.
Having read several articles about this is seems that SQL Server 2005 is
more optimised and therefore it ignores the ORDER BY in the SELECT Statement.

If I change the recordsource property to SELECT * FROM (name of the view)
ORDER BY (fields) then the order of the row are correct, however I then get
an error after inserting a row which says: Your row was added to the
database but will not be viewable as it does not satisfy the underlying
criteria.

Question: How do I move forward from here? I have only been able to
resolve the issue by copying the select statement (together with the ORDER By
clause) from the view into the record source property on the form.
This is not a neat solution and I therefore would like to know if anyone has
found a better solution.

 

[Michel Walsh]

Hi,

It sounds that you have a dynamic recordset.

If you say:

SELECT a.* FROM myTable WHERE unitPrice <= 10


and then, select a record, modify its unit price to 11, with a dynamic
recordset, the modified record FALLS OUTSIDE the criteria and cannot be
reach anymore! That is the essence of a dynamic recordset, to re-evaluate
the membership of the record.

Instead, with a keyset recordset, the membership is NOT re-evaluated AFTER
you get the initial opening of your recordset. In the previous example,
setting a unit price to 11 would KEEP the record in the recordset even if it
does NOT satisfy the initial criteria. Recordsets opened by Jet and which
are called "dynaset" are, in fact, of this category.

With ADO, against MS SQL Server, you can use both dynamic recordset, or
keyset recordset. Against JET, when you ask a dynamic recordset, you get, in
reality, a keyset recordset since JET does not support dynamic recordsets.


In MS SQL Server 2000, to get the order by on a view, you have to SELECT TOP
100 PERCENT .... ORDER BY .... (in fact, if you don't use TOP 100 PERCENT
on a view, with an order by, an error message telling you to add the TOP 100
PERCENT is likely to occur). Unfortunately, TOP, with MS SQL Server, makes
the query not updateable (while it is not necessary the case with JET).
Also, unless you are careful or unless you use MSAccess-dot-adp, when you
use a view you are like to be only able to append data to just one of the
table of the view (but you can add "Instead Of Triggers" to remove that
restriction, or, as I mentioned, use a form in MS Access environment). For
the Instead Of Trigger and update on a view, see Delaney in her "Inside
Microsoft SQL Server 2000", Microsoft Press, pages 358-359.



Hoping it may help,
Vanderghast, Access MVP

 

[giorgio rancati]

Hi Alister,

You could force the view order by with the new Sql2005 ROW_NUMBER() Function
----
USE [NorthwindCS]
GO

Create view [dbo].[uvs_Products]
AS
SELECT ROW_NUMBER() OVER (ORDER BY ProductName) AS RecNo,
*
FROM dbo.Products

 

[MGFoster]

The ORDER BY clause in a VIEW goes against the SQL standard. MS has
just corrected a prior non-standard implementation of SQL Server (before
SQL2005).