Francisco Tapia
fhtapia at gmail.com
Tue May 24 09:51:06 CDT 2011
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 > >