jwcolby
jwcolby at colbyconsulting.com
Mon Jun 6 10:17:01 CDT 2011
I stayed away from ADPs because the rumor mill said they were being deprecated. Now I hear they are not. The Select top(Huge number) trick actually returned a sorted dataset so I kind of don't have the problem any more. John W. Colby www.ColbyConsulting.com On 5/24/2011 10:51 AM, Francisco Tapia wrote: > John, > Having worked between Access and Sql Server for a long time, I can tell > you that what you are trying to do is many times do-able when your Access > front end is an "Access Data Project" vs a normal Access Database. and the > "bound" methods that you normally use are still available. Though, you will > need to jump a whole new set of hoops just to make your bound forms work as > you wanted. Let's not mistake this as a "bound vs unbound" unholy war, but > I'm just saying. There may be a need for you to extend your framework to > handle this situations so that you can leverage your "bound" project. > > > > -Francisco > http://bit.ly/sqlthis | Tsql and More... > http://db.tt/JeXURAx | Drop Box, Storage in the Cloud (free) > > > > > On Mon, May 23, 2011 at 3:41 AM, jwcolby<jwcolby at colbyconsulting.com>wrote: > >> Mark, >> >> AFAICT pass through queries are not editable. I am trying to build three >> specific types of queries. >> >> 1) Editable (bound) form queries >> 2) Uneditable combo queries >> 3) Uneditable report queries. >> >> I am using Access 2K3 for dev and Access 2K7 (runtime) someday soon to be >> 2K10 (runtime) for general usage. >> >> It seems that the top(very large number) works for the moment for returning >> sorted recordsets. OTOH doing the pass-through trick for doing filtered >> uneditable queries would work great. >> >> >> John W. Colby >> www.ColbyConsulting.com >> >> On 5/23/2011 4:21 AM, Mark Breen wrote: >> >>> Hello All, >>> >>> I too have seen SQL Server / Access not return the sorted records. I am >>> intrigued by Francisco's suggestion to try the 99.99...... options, but I >>> am >>> in the habit now of never ordering within a view and always ordering when >>> i >>> select data from a view. >>> >>> In the cases where I can use an sproc, then I do not use a view at all, >>> just >>> include the select that would go in the view in the sproc instead. Then I >>> can safely sort in the sproc and no worries about the client end. >>> >>> John, may I tell you about a trick that I sometimes do, and please exclude >>> me if you are already doing this. I create a Past Through query in >>> Access, >>> which as you know, ignores jet and sends the query straight to the source >>> db >>> (SQL Server in this case). I then programatically change the 'SQL' of the >>> qdef based on what I want to do. Sometimes, I just call the pass-through >>> query qpstTemplate. The template bit being the connection string. >>> >>> Sometimes I have two, qpstTemplate_ReturnsRecords and >>> qpstTemplate_NoRecords. The 'returns no records' flag is set in the >>> second >>> one and I can use that for action sprocs. >>> >>> I then set the sql to be *usp_GetCustomers*, then later set it again to * >>> usp_GetOrdersByOrderDate* >>> >>> As a result, I often do not need to sort within Access. As you >>> instinctively know, asking Jet to do this work is not the right course. >>> Do >>> you want to let SQL server do the heavy lifting for you. >>> >>> Any help? >>> >>> thanks >>> >>> Mark >>> >>> >>> >>> On 21 May 2011 10:33, Asger Blond<ab-mi at post3.tele.dk> wrote: >>> >>> Thanks for the explanation, Stuart. Still I've never seen the result you >>>> indicate. The TOP 100 PERCENT is a special case where the query engine >>>> will >>>> ignore the ordering specified since you are actually requesting all rows. >>>> But if you force the engine to make a selection using TOP 99.999999999 >>>> PERCENT then it makes no sense to me why the engine should not return the >>>> rows in the order it's being forced to use for the selection. And >>>> certainly >>>> if you use TOP 5 it would be quite inefficient for the engine not to >>>> return >>>> the ordered rows. So: when the engine is forced to use a condition (TOP 5 >>>> or >>>> TOP 99.999999999) I've never seen a situation where the rows are not >>>> returned in the order specified by the query - whether this is embedded >>>> in a >>>> view or just run as a plain query. >>>> Asger >>>> >>>> -----Oprindelig meddelelse----- >>>> Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto: >>>> dba-sqlserver-bounces at databaseadvisors.com] På vegne af Stuart McLachlan >>>> Sendt: 21. maj 2011 06:29 >>>> Til: Discussion concerning MS SQL Server >>>> Emne: Re: [dba-SQLServer] Views don't sort >>>> >>>> It means that if you specify "SELECT TOP 5 FROM ALPHABET ORDER BY >>>> LETTER", >>>> you >>>> will always get A,B,C,D and E, but they may not necessarily be in that >>>> order. They may be >>>> returned as "E,D,C,B,A", "D,B,A,E,C" etc. >>>> >>>> Although it has worked up til now, it is just like any other hack that >>>> uses >>>> "undocumented >>>> features". - after the next patch, hotfix or service pack, you may find >>>> that it no longer works >>>> that way - the same records could be returned in a completely different >>>> sort order, possibly by >>>> PK or by the order in which they are physically stored on disk. >>>> >>>> -- >>>> Stuart >>>> >>>> On 21 May 2011 at 0:09, Asger Blond wrote: >>>> >>>> Well, and this quote from BOL just doesn't make any sense to me. The >>>>> TOP and ORDER BY clause is used to "determine the rows returned", but >>>>> it "does not guarantee ordered results" - WTF does this mean? I use >>>>> the construct specified by Francisco, and have never seen problems. >>>>> Asger >>>>> >>>>> -----Oprindelig meddelelse----- >>>>> Fra: dba-sqlserver-bounces at databaseadvisors.com >>>>> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af >>>>> Stuart McLachlan Sendt: 20. maj 2011 22:48 Til: Discussion concerning >>>>> MS SQL Server Emne: Re: [dba-SQLServer] Views don't sort >>>>> >>>>> Be careful with that. It is not guaranteed to work! >>>>> >>>>> See http://msdn.microsoft.com/en-us/library/ms188385.aspx >>>>> <quote> >>>>> The ORDER BY clause is not valid in views, inline functions, derived >>>>> tables, and subqueries, unless TOP is also specified. ... When ORDER >>>>> BY is used in the definition of a view, inline function, derived >>>>> table, or subquery, the clause is used only to determine the rows >>>>> returned by the TOP clause. The ORDER BY clause does not guarantee >>>>> ordered results when these constructs are queried, unless ORDER BY is >>>>> also specified in the query itself</quote> >>>>> >>>>> The only way to be sure is to use "Select * from vwMyView Order by >>>>> colMyCol" >>>>> >>>>> -- >>>>> Stuart >>>>> >>>>> On 20 May 2011 at 13:31, jwcolby wrote: >>>>> >>>>> Francisco, >>>>>> >>>>>> I apparently ignored your top 99.999% part. >>>>>> >>>>>> When I went back in to my view and selected top 1 million (very big) >>>>>> it did in fact return a sorted data set. >>>>>> >>>>>> Thanks! >>>>>> >>>>>> John W. Colby >>>>>> www.ColbyConsulting.com >>>>>> >>>>>> On 5/20/2011 1:07 PM, Francisco Tapia wrote: >>>>>> >>>>>>> So something like: >>>>>>> >>>>>>> Create View vwSomeView AS >>>>>>> Select TOP 99.9999 percent Field1, Field2, Field3 >>>>>>> >>>>>>>> From tblSomeTable >>>>>>>> >>>>>>> Order by Field3 >>>>>>> >>>>>>> does not sort field3? what are your results when you just select >>>>>>> * from vwSomeView ? are the results sorted in your results >>>>>>> display in management studio? >>>>>>> >>>>>>> >>>>>>> -Francisco >>>>>>> <http://bit.ly/sqlthis> >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Fri, May 20, 2011 at 10:02 AM, >>>>>>> jwcolby<jwcolby at colbyconsulting.com>wrote: >>>>>>> >>>>>>> One of the things I am trying to do is use SQL Server to speed up >>>>>>>> my applications. The theory is that I can hand off the heavy >>>>>>>> lifting to SQL Server and just get back result sets. Of course >>>>>>>> this works in terms of joins and filters in a view, but even >>>>>>>> though I specify a sort in a view, when the result set hits the >>>>>>>> other end (Access in my case) it is unsorted. >>>>>>>> >>>>>>>> Views have the ability to do sorts, so why is the data returned >>>>>>>> by a view into a third party app, or even into another view in >>>>>>>> SQL Server unsorted? >>>>>>>> Is there a way to tell sql server to return sorted data? >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>> dba-SQLServer mailing list >>>>>>> dba-SQLServer at databaseadvisors.com >>>>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>>>>> http://www.databaseadvisors.com >>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>> dba-SQLServer mailing list >>>>>> dba-SQLServer at databaseadvisors.com >>>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>>>> http://www.databaseadvisors.com >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> dba-SQLServer mailing list >>>>> dba-SQLServer at databaseadvisors.com >>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>>> http://www.databaseadvisors.com >>>>> >>>>> >>>>> _______________________________________________ >>>>> dba-SQLServer mailing list >>>>> dba-SQLServer at databaseadvisors.com >>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>>> http://www.databaseadvisors.com >>>>> >>>>> >>>> >>>> >>>> >>>> _______________________________________________ >>>> dba-SQLServer mailing list >>>> dba-SQLServer at databaseadvisors.com >>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>> http://www.databaseadvisors.com >>>> >>>> >>>> _______________________________________________ >>>> dba-SQLServer mailing list >>>> dba-SQLServer at databaseadvisors.com >>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>> http://www.databaseadvisors.com >>>> >>>> >>>> _______________________________________________ >>> dba-SQLServer mailing list >>> dba-SQLServer at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>> http://www.databaseadvisors.com >>> >>> >>> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >