Access 2002 ADP & SQL 2005 Views

[Guest]

Access 2002 ADP & SQL 2005 Views

Recently we upgrade from Access 2002 ADP & SQL 2000, to Access 2002 ADP &
SQL2005.
This is cause a a few problems which we are trying to sovle.




Problem 1:
I a form which is based on a view for recordset, when I delete a record from
the form I get the message: "The data was added to the database but the data
won't be displayed in the form because it doesn't satisfy the criteria in the
underlying record source."
I was able to fix this by setting the "Unique Table" property of the form.
Although we didn't have to set this property when we were using SQL2000

So far so good. When I try to insert a new record I get this message: "The
data was added to the database but the data won't be displayed in the form
because it doesn't satisfy the criteria in the underlying record source."
I tried to fix this problem by setting the "Resync Command" property.
When I set this property to something (anything! even just a single
character) then the message isnt't displayed and the record is saved and
displayed properly - but with one exception. The primary key, which is also
autonumber is always displayed as 0. Although after I close and open the
form, the data appears that was written fine.

The only way I was able to fix this was by changing the data source of the
form, from a View, to a hard coded SQL statement identical to the View.
In this case the form works fine, and the autonumber primary key is always
displayed properly.

Any suggestions? It is very strange to me as to why the"Resync Command"
works by just putting anything inside it. But even then how can I make the
primary key autonumber field appear properly?





Prolem 2:
Something is definately wrong with sorting.
When I open a form which is based on a view for recordset, the sorting order
of the form is completely different from the sorting order of the view.

Again the only I was able to fix this was by changing the data source of the
form, from a View, to a hard coded SQL statement identical to the View.

What is happening here ? The solution is the same as in problem 1!?? Could
it be that Access has trouble dealing with SQL 2005 views? What can I do to
make the sort work with setting the data source to a hard coded statement?

 

[Guest]

Correction:
What can I do to make the sort work WITHOUT setting the data source to a
hard coded statement?

 

[Sylvain Lafontaine]

Easy: replace the view with a stored procedure.

 

[Sylvain Lafontaine]

When you set the ResyncCommand to an unrecognisable statement, you will
revert to the default ADO resync command, which is simply to return the
newly entered values without making any round trip to the server to verify
that the values are OK or have not been modified by a trigger or something
else.

Instead of Views, you should always use a hardcoded statement (best
performance under ADP because you will diminish the number of unnecessary
requests) or a stored procedure (SP) with ADP.

Excerpt when using with a TOP n Percent where n <> 100, sorting order are
not part of the View statement and are stripped by SQL-Server 2005. The
fact that SQL-Server 2000 doesn't remove it when you use a sorting order
with a TOP 100% is simply a bug from the optimizer of SQL-Server 2000.

 

[David Portas]

Excerpt when using with a TOP n Percent where n <> 100, sorting order are
not part of the View statement and are stripped by SQL-Server 2005. The
fact that SQL-Server 2000 doesn't remove it when you use a sorting order
with a TOP 100% is simply a bug from the optimizer of SQL-Server 2000.

It's worth clarifying that a bit more. SQL Server views are not sorted
under ANY circumstances. That is to say, the ordering of a query
against a view is always undefined unless you specify ORDER BY
explicitly in that query. TOP n or TOP n PERCENT makes no difference to
this *irrespective* of the value of n. TOP n in a view may or may not
appear to change the actual order of rows returned at runtime but it
doesn't affect the *expected* ordering of the result in any way.

The purpose of TOP n is purely to define the *selection* of rows to be
returned (n rows or n% of the rows). Unfortunately, the confusion about
sorting in views arises because the TOP syntax uses the ORDER BY clause
as a way of specifying which n rows to return. ORDER BY therefore
serves a double purpose in queries but not in views (because sorting
doesn't apply to views). In conclusion, this was a really awful piece
of syntax design by the SQL Server team!

This behaviour is still the same in 2000 and 2005 but that hasn't
stopped some people just assuming that views will always sort the same
way even when queries against the view don't specify ORDER BY.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Robert Morley]

I'm confused...I use ORDER BY in views in SQL Server 2000 all the time, and
the results come back in the expected order. Under what circumstances
exactly is ORDER BY ignored?



Rob

 

[Guest]

Thank you for your comments on sorting guys. The solution I ended up going
with was to take out all the ORDER BY statements from the VIEWS and then
going to each form and changing the datasource from "ViewXXX" to "SELECT *
FROM ViewXXX ORDER BY MyField" and now sorting works fine.
Sylvain this was faster for me than writing new stored procedures. Please
let me know if you think I am doing something very wrong here!


However I am still confused about what to do for problem1:
I am still using a view for my form and thus everytime I insert a new record
the autonumber field show up as 0. I have tried lots of syntax variants for
the resync command but I always get 0 for the autonumber field. As I
mentioned in my first post, it only shows the real autonumber if I use a
hardcoded statement for the form's data source. Can this be solved by still
using a view as a data source?





Small personal note:
I can't change the form's datasource to a stored procedure because if I do
this, the ServerFilter property that I use to filter the form stops working,
and thus I presume that I would have to write parametric stored procedure in
order to be able to filter the form. And this would require drastic changes.
It is still unclear to me if I should be using this as a best solution for
filtering my forms.

 

[Robert Morley]

Having asked the question, it occurs to me that I almost exclusively use
ORDER BY in Views that I look at either from Access Queries or SSEM Views.
Access Forms are a whole other ball game, and I got used to using the form's
sorting options even before I converted to SS2k, so maybe that's why I don't
have any problems with ORDER BY, but the OP does?


Rob

 

[dbahooker]

just for the record, problem #1 might have had a completely different,
easier resolution.

-Aaron