ORDER BY in View and ADP Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName
 
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName
Click to expand...


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
--
 
This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead of
a view?

David said:
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName
Click to expand...


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
--
Click to expand...
 
Yes. For more complicated select requests, don't forget to set the
UniqueTable and Resync Command properties if you want them to be updatable.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead of
a view?

David said:
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName
Click to expand...


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
--
Click to expand...
Click to expand...
 
Or you could just set the RecordSource to:

SELECT * FROM vwPatientInformation ORDER BY LastName, FirstName, MiddleName

This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead of
a view?

David said:
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName
Click to expand...


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
--
Click to expand...
Click to expand...
 
i disagree keep everything in a view unless it MUST be in a sproc

for starters; views allow you to use 'Analysis services'

try building a cube based off of a sproc lol



Or you could just set the RecordSource to:

SELECT * FROM vwPatientInformation ORDER BY LastName, FirstName, MiddleName

This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead of
a view?

David said:
(e-mail address removed) wrote:
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
--
Click to expand...
Click to expand...
Click to expand...
 
I don't think it's me you are disagreeing with (for once).

i disagree keep everything in a view unless it MUST be in a sproc

for starters; views allow you to use 'Analysis services'

try building a cube based off of a sproc lol



Or you could just set the RecordSource to:

SELECT * FROM vwPatientInformation ORDER BY LastName, FirstName, MiddleName

This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead of
a view?

David Portas wrote:
(e-mail address removed) wrote:
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
Click to expand...
Click to expand...
Click to expand...
 
you're right

im disagreeing with that David Portas

why should everything be a sproc, david? i mean-- please tell us
please enlighten us

OH YEAH
I forgot.. Microsoft finds BEANER RETARDS TO BE MVPs. ANYONE
ENLIGHTENED WITH SQL ISNT ELIGIBLE FOR ACCESS MVP.

WHY DOES EVERY ACCESS MVP DIPSHIT IN THE WORLD NOT UNDERSTAND THE
CONCEPT OF ADP?

Why aren't there any MVPs-- the people with the credibility to work for
*CHANGE* that PUSH MICROSOFT TO REMAIN COMMITTED TO ADP?

bunch of god-damn WUSSES
lose the training wheels



I don't think it's me you are disagreeing with (for once).

i disagree keep everything in a view unless it MUST be in a sproc

for starters; views allow you to use 'Analysis services'

try building a cube based off of a sproc lol



Or you could just set the RecordSource to:

SELECT * FROM vwPatientInformation ORDER BY LastName, FirstName, MiddleName

This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead of
a view?

David Portas wrote:
(e-mail address removed) wrote:
When I open up a form in an Access ADP to access this view, the rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005. Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix, DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set is
reasonably small.

--
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
Click to expand...
Click to expand...
Click to expand...
 
So you are admitting now that Microsoft is NOT committed to ADP's. Not only
are you childish and offensive, you aren't even consistent.

you're right

im disagreeing with that David Portas

why should everything be a sproc, david? i mean-- please tell us
please enlighten us

OH YEAH
I forgot.. Microsoft finds BEANER RETARDS TO BE MVPs. ANYONE
ENLIGHTENED WITH SQL ISNT ELIGIBLE FOR ACCESS MVP.

WHY DOES EVERY ACCESS MVP DIPSHIT IN THE WORLD NOT UNDERSTAND THE
CONCEPT OF ADP?

Why aren't there any MVPs-- the people with the credibility to work for
*CHANGE* that PUSH MICROSOFT TO REMAIN COMMITTED TO ADP?

bunch of god-damn WUSSES
lose the training wheels



I don't think it's me you are disagreeing with (for once).

i disagree keep everything in a view unless it MUST be in a sproc

for starters; views allow you to use 'Analysis services'

try building a cube based off of a sproc lol




Baz wrote:
Or you could just set the RecordSource to:

SELECT * FROM vwPatientInformation ORDER BY LastName, FirstName, MiddleName

This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure
Click to expand...
instead
of
a view?

David Portas wrote:
(e-mail address removed) wrote:
When I open up a form in an Access ADP to access this view,
Click to expand...
the
rows
are not sorted. I am using MS Access 2003 and MS SQL Server
Click to expand...
2005.
Is
there a reason why the rows are not sorted? Here is the code I used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName,
Click to expand...
Suffix,
DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result
Click to expand...
set
is
reasonably small.

--
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
Click to expand...
Click to expand...
Click to expand...
 
Microsoft _IS_ commited to ADP.

but they're not COMMITTED ENOUGH.

there are still thousands of RPG developers sitting around; building
simple data entry forms for DB2 databases.

ADP is a vastly superior solution.

Microsoft has come out and said that they are committed to it; and that
the next version will support design changes to SQL 2005.

Go play with some mental midgets, Sbaz

-Aaron

So you are admitting now that Microsoft is NOT committed to ADP's. Not only
are you childish and offensive, you aren't even consistent.

you're right

im disagreeing with that David Portas

why should everything be a sproc, david? i mean-- please tell us
please enlighten us

OH YEAH
I forgot.. Microsoft finds BEANER RETARDS TO BE MVPs. ANYONE
ENLIGHTENED WITH SQL ISNT ELIGIBLE FOR ACCESS MVP.

WHY DOES EVERY ACCESS MVP DIPSHIT IN THE WORLD NOT UNDERSTAND THE
CONCEPT OF ADP?

Why aren't there any MVPs-- the people with the credibility to work for
*CHANGE* that PUSH MICROSOFT TO REMAIN COMMITTED TO ADP?

bunch of god-damn WUSSES
lose the training wheels



I don't think it's me you are disagreeing with (for once).

i disagree keep everything in a view unless it MUST be in a sproc

for starters; views allow you to use 'Analysis services'

try building a cube based off of a sproc lol




Baz wrote:
Or you could just set the RecordSource to:

SELECT * FROM vwPatientInformation ORDER BY LastName, FirstName,
MiddleName

This form is for data entry. Will the user be able to modify, insert,
or delete records if the form is based on a stored procedure instead
of
a view?

David Portas wrote:
(e-mail address removed) wrote:
When I open up a form in an Access ADP to access this view, the
rows
are not sorted. I am using MS Access 2003 and MS SQL Server 2005.
Is
there a reason why the rows are not sorted? Here is the code I
used:

CREATE VIEW [dbo].[vwPatientInformation]
AS
SELECT TOP (100) PERCENT LastName, MiddleName, FirstName, Suffix,
DOB,
MedicalRecordNumber, Prefix
FROM dbo.Patients
ORDER BY LastName, FirstName, MiddleName


Views have no fixed ordering in SQL Server. In that respect a view
behaves exactly like a table. The sort order returned by a query
against a view is undefined unless you specify ORDER BY in the query
(not the view).

The TOP 100 PERCENT modifier in your view does nothing useful so I
recommend you remove it and put your query in a stored procedure
instead. Access also gives you the option of doing the sorting
client-side, which is probably not desirable unless the result set
is
reasonably small.

--
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
Click to expand...
Click to expand...
Click to expand...
 
Hi,
ADP is a vastly superior solution.
Click to expand...

Well, not the technical most superior solution sells best, but the version
which is best advertised. And MS obviously isn't to keen in selling (and
therefore developing further) the technical most superior solution (= ADP)
Microsoft _IS_ commited to ADP.
Click to expand...

yes, the next 22 month
but they're not COMMITTED ENOUGH.
Click to expand...

Well, if you think so, then write a letter to (e-mail address removed) <bg>

Here's what i read elsewhere ...

<snip>
Does the Access team have any choice but to distance Access from 'classic'
ADO? Mainstream support for MDAC 2.8 ends in 22 months
(http://support.microsoft.com/lifecycle/?p1=1596). I haven't heard of any
plans for a new release?
<End Snip>

AND read carefully the recommendation Mr. Rucker gives:

(http://blogs.msdn.com/access/archive/2006/07/27/680772.aspx): "Access
project files, also called ADPs or .adp files, provide an alternative to
using the Access database engine by allowing an Access application to
connect directly to the tables in a SQL Server database. ADPs will continue
to be supported in Office Access 2007. To take maximum advantage of the new
features in Office Access 2007, Microsoft recommends the use of linked
tables to connect to SQL Server data." ...

And

....The primary application programming interface (API) for working with the
Microsoft Jet database engine from code is Data Access Object (DAO). In
Office Access 2007, new objects, properties, and methods will be added to
DAO to support the new features in the Access database engine. For example,
multi-valued fields created using the new Lookup Wizard will be accessible
from code as DAO recordsets. In Office Access 2007, programmers will also
have the option of enhancing their Access applications with add-ins and
smart panes based on managed Microsoft .NET code. A primary interop assembly
(PIA) for Access, as well as one for DAO, will enable managed code running
in these add-ins and smart panes to manipulate Access user interface objects
and Access data. Office Access as a Developer Tool Access is most notable in
how it enables information workers to build applications that otherwise
would require a professional developer. However Read More ...

And last not least:

Pls show me the new Layout View and the Allow Layout View property in an ADP
file, I can't find it...

MS advertising (it's not me !!) says loud and clear:

If you're a profi programmer, programming for MS products, then you use
Visual Studio and the compilers within. Ahh, you don't use Visual Studio
(and therefore VB.NET / C#.NET etc.) well, then you can't be a profi
programmer. For our special database programmers out here we even offer
http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx
this special version.

Ahh, you have no money but you want to develop - well, then we have all the
Express Editions for you...

Before you saying anything against me: Nope, i don't like it either, but i
don't close my eyes and i see what's happening, so pls. don't be unpolite
again. It's not my fault.
 
Hi Klaus,

I am the author of the question you quoted. I would just like to stress that
it was exactly that - a question. My hope was that someone from Microsoft
might respond and clarify the situation. But as you know, that did not
happen. The question remains unanswered.
 
Hi Brendan,
I am the author of the question you quoted. I would just like to
stress that it was exactly that - a question. My hope was that
someone from Microsoft might respond and clarify the situation. But
as you know, that did not happen. The question remains unanswered.
Click to expand...

i see it that way:
(a german saying) - no answer in itself as well is an answer.
I'm rather sure, that you won't get an official answer from MS at all.
(The only inofficial answer from MS to that question was: no comment)
 
I don't disagree with your conclusions, Klaus, I just wanted to clarify the
status of that particular quote.
 
go screw yourself

conflicting statements everywhere that I see.

ADP is a much much better solution.
Anyone that disagrees is a ****ing pussy and shouldn't be writing
software professionally.

And any project manager at Microsoft-- that decides that it is somehow
more important-- to sell us on a crappy-ass solution?

well you and I have an appointment at 60 acres in Redmond

I mean seriously here.

You MDB - wimps use the same work arounds for the same damn bugs.. I
don't have to spend any time linking and refreshing; setting up DSN.

I get better performance.
I have better scalability and better extensability.

Have you dipshits ever SOLD ADP to a customer?
Well maybe you ****ing should.

BECAUSE THEY ****ING EAT IT UP.

Bunch of pussy ass MDB folks; oh bring back DAO--

****ing retards

I can do anything that you can do-- with a single object model.

what.. do you need to enum queries?
do you need to enum tables?

****ing retards

lose the training wheels you ****ing pussies

I; for one-- am sick and tired of working with this MDB _CRAP_

**** you guys.
you guys spread NOTHING but misinformation

ADP are continued; they allow updates to SQL 2005. I've seen it from
MIcrosoft.
and anyone that tells you otherwise?

they're a bunch of 60-year-old PUSSIES that don't have the mental
capacity to learn SQL Server.

I mean; for ****s sakes-- DAP will even be supported until 2013.. I've
seen it in writing, from Microsoft.

And you're saying that MDAC 2.8-- which shipped with Windows XP-- isn't
going to be around until 2015?

you guys are a bunch of alarmist DIPSHITS.

go **** yourselves, kids.

I'm an Access ADP MVP; and since ADP is superior to MDB; I am hereby
PULLING RANK and removing your MVP from all you MDB MVP _PUSSIES_.

Lose the ****ing training wheels you ****ing dipshits



-Aaron
ADP Nationalist
Access ADP MVP
 
please note that their 'data dude' doesn't even support MDB files.

you ****ing flaming ass pussies

MDB is for dipshits.

I've NEVER seen it in plain english-- that Microsoft is going to
discontinue ADP.
Just because new methods are going to be added to DAO-- does that mean
that DAO is the one and only data layer?

this doesn't say that ADP won't be supported.
that doesn't mean that MDB are more powerful than ADP.
this doesn't mean that ADP aren't still going to be superior.

ADP and MDB are both going away.
you can use ACCDB files to point to either SQL Server or you can ****
yourselves and keep your data in a ACCDB file.

Just because it's an OPTION; it doesn't mean that it's SUPERIOR.
Just because it's RECCOMENDED; that doesn't mean that ADP is going
away.

ADP will support changes to SQL 2005. I've seen it in writing.

I've been at dozens of companies; writing Access Data Projects.
and i've had countless customers that are FLOORED when they see how
easy it is.

Development isn't easy in MDB.
Development is jumping through hurdles; and applying workarounds.

Just because some dipshit wrote a simple module in MDB 10 years ago;
does that mean that MDB is more powerful?

Can you right click on a MDB file and 'create script'

why don't you **** yourselves until you MDB pansies have 1/4th of the
functionality of SQL Server.
 
Back
Top