Sort SQL

[Jamie Collins]

I try never to rely on either the default ordering or the physical
ordering of an Access table. It seems SQL purists see a view in the
same fashion. I didn't know that. You say that both tables and views
should be considered unordered until you get to a separate query that
contains an "ORDER BY" clause.

If my front end (business objects, report writer, etc) relies on the
resultset being in a certain order (e.g. to do procedural processing)
then generally speaking I will sort the resultset on the 'client side';
this usually translates to calling the recordset object's Sort method.
Think of it the other way around: I get a recordset from a
VIEW/Query/PROC and it must be in a certain order for my routine to
produce meaningful results so I'll sort the recordset; if the resultset
had already been sorted in the SQL then one of the operations is
redundant and I'd say sorting the recordset is not overly paranoid.

Passing a 'sort order' parameter to a SQL procedure, to be able to do
'server side' sorting, usually involves messy, usually dynamic, SQL. In
some cases (ADO recordset with SQL Server) performing the sort in the
middleware can yield better performance.

Jamie.

--

 

[James A. Fortune]

James A. Fortune wrote:




If my front end (business objects, report writer, etc) relies on the
resultset being in a certain order (e.g. to do procedural processing)
then generally speaking I will sort the resultset on the 'client side';
this usually translates to calling the recordset object's Sort method.
Think of it the other way around: I get a recordset from a
VIEW/Query/PROC and it must be in a certain order for my routine to
produce meaningful results so I'll sort the recordset; if the resultset
had already been sorted in the SQL then one of the operations is
redundant and I'd say sorting the recordset is not overly paranoid.

Passing a 'sort order' parameter to a SQL procedure, to be able to do
'server side' sorting, usually involves messy, usually dynamic, SQL. In
some cases (ADO recordset with SQL Server) performing the sort in the
middleware can yield better performance.

Jamie.

O.K. Doing sorting on the 'client' side makes a lot of sense. By not
relying on a table's default order in Access I use an ORDER BY in a
query and don't consider that overly paranoid either. Since we're
talking about Access and, say, SQL Server as a backend, what software do
you use for doing a middleware sort? Excel ?

Thanks,

James A. Fortune
(e-mail address removed)

 

[aaron.kempf]

don't use MDB for anything... use Access Data Projects exclusively
if you want to do something like that; then just use this code

Public Sub OpenTempQuery(strSQL As String)
myTempProc = "HELLO_WORLD"
strSql = "CREATE PROC " & myTempProc & "AS " & strSql
DoCmd.OpenStoredProcedure strQdf
' delete temporary sproc, if it exists
DoCmd.RUnsql "IF EXISTS (Select Name From Sysobjects Where Name =
'" & myTempProc & "' AND Xtype = 'P') BEGIN DROP PROCEDURE " &
myTempProc & " END"
End Sub

or more importantly; just use Query Analyzer... and don't worry about a
temp query lol

Access MDB is for losers dont use it or you will be looked down upon by
'real database people'


-Aaron

To open a query in datasheet view based on an SQL statement add the following
procedure to a standard module:

Public Sub OpenTempQuery(strSQL As String)

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strQdf As String
Static n As Integer

Set dbs = CurrentDb

n = n + 1
strQdf = "qdfTemp" & n

' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)

DoCmd.OpenQuery strQdf

' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name

End Sub

You can then call it like so:

Dim strSelectSQL As String

strSelectSQL = "SELECT MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeyword"

OpenTempQuery strSelectSQL

By incrementing the numeric suffix to the name of the temporary querydef
object each time the procedure is called you can have several temporary
querydef objects open simultaneously, e.g. you could call the procedure once
and order the query by one column, then again ordered by another column and
have both showing in datasheet view at the same time.

Ken Sheridan
Stafford, England

Dear All,

I do not undestand why this code is not working, I Only want to Sort
Ascending
The Field MyKeyword from the Tbl MyKeywords_Tbl

--->My Code:

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


CurrentDb.Execute strSelectSQL, dbFailOnError

rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing


---->I Get a Run-time error 3065*
"Cannot execute a select query."

I Found out The execute method only works with Action Queries (Update,
Append and Make
Table)

So I added and changed to my code:

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim strSelectSQL As String

Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)

DoCmd.RunSQL strSelectSQL

I Get a Run-time error 2342

"A RunSQL action requires an argument consisting of an an SQL
statementet"

-----> I did change :

DoCmd.RunSQL strSelectSQL to DoCmd.OpenQuery strSelectSQL

and I get another

Run-time error 7874

Microsof acces can't find the object 'SELECT
MyKeywords_Tbl.MyKeyword FROM Mykeywords_tbl ORDER BY
MyKeywords_Tbl.Mykeyword;.'


->>>>Please help me what I am doing wrong???

->>>>>@Dim db_Keyword As Database" is wrong? it shoul be "Dim
db_Keyword As DAO.Database"?

->>>>>>Also what is the diference between CurrentDb.Execute
strSelectSQL, dbFailOnError &
DoCmd.RunSQL strSelectSQL?

--->This is my entire code, Maybe is something wrong:

Thanks in advance
Antonio Macias

Private Sub Keyword_AfterUpdate()

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim SQL3, sQL4, strSelectSQL As String 'Select Query Table
Dim Msg, Style, Title, Response, MySelection
Dim MyCuKeywordID As Variant 'My Current
Keyword ID
Dim MyReSeKeyword As Variant 'My Record
Search Keyword
Dim MyToReCo_Keywords_Tlb As Variant 'My Total
Records Keywords_Tbl


'Make sure Microsoft DAO 3.6 Library in included in the References

'Initialize Variables
Msg = "Are you sure " & "[ " & Keyword & " ]" & " Will be your
default search?"
msg1 = Keyword & " Is now your default search"
msg2 = "The Keyword: " & "[ " & Keyword & "]" & " Already Exists,
Please enter another Keyword"
Style = vbYesNoCancel + vbQuestion 'Define buttons.
Style1 = vbYesNo + vbInformation 'Define buttons.
Style2 = vbOKOnly + vbInformation 'Define buttons.
Title = "Setting Keyword" 'Define title.
Title1 = "Keyword Set" 'Define title.
Title2 = "Keyword Dulicated" 'Define title.



MyReSeKeyword = _
DLookup("[MyKeyword]", "MyKeywords_Tbl", "[MyKeyword] =
'" & Keyword & "'")

If IsNull(MyReSeKeyword) Then

Response = MsgBox(Msg, Style, Title)
If Response = 6 Then 'User chose Yes.
MySelection = "Yes" 'Perform some action.

Response1 = MsgBox(msg1, Style1, Title1)

If Response1 = 6 Then

'This SQL Statement UPDATE All the Field MyKeyword OF THE
'Tbl MainExclude_Tbl With a Control Source (Keyword)

'This is an Action querie (Update, Append, and Make Table)

'When is an UPDATE Query and the Control Source Comes from
a
'Form the sintaxis has to be '" & Contol Source &"' ended
with ; and closed
'With a quote.
SQL3 = _
"UPDATE MainExclude_Tbl SET MainExclude_Tbl.MyKeyword = '"
& Keyword & "'"

'Please read Write Conflict Form Subform.pdf
If Me.Dirty Then Me.Dirty = False

CurrentDb.Execute SQL3, dbFailOnError

'Searchs the First occurrence that contains the Control
Source (Keyword)
'in the Tbl (MainExclude_Tbl) and Returns its ID, to ensure
that
'the DLookup function returns a unique value:

MyCuKeywordID = _
DLookup("[ID]", "MainExclude_Tbl", "[MyKeyword] = '" &
Keyword & "'")

'This SQL Statement INSERT INTO the Tbl (MyKeywords_Tbl) the
Control Source
'(Keyword) FROM the Tbl MainExclude_Tbl, WHERE "ONLY" INSERT
FROM Tbl
'MainExclude_Tbl The Variable MyCuKeywordID:

sQL4 = _
"INSERT INTO MyKeywords_Tbl ( MyKeyword ) " & _
"SELECT MainExclude_Tbl.MyKeyword " & _
"FROM MainExclude_Tbl " & _
"WHERE (((MainExclude_Tbl.ID) = " & MyCuKeywordID & " ))"

CurrentDb.Execute sQL4, dbFailOnError

------------------------ My Problem Here
--------------------------------------------------

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)


DoCmd.OpenQuery strSelectSQL



rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing

-------------------------------------------------------------------------------------------------------------------------------

ElseIf Response1 = 7 Then
Keyword = ""
MsgBox "User Cancel", vbInformation, "Canceled
default Search"
End If

ElseIf Response = 7 Then 'User chose No.
MySelection = "No" 'Perform some action.
Keyword = ""
ElseIf Response = 2 Then 'User chose Cancel.
MySelection = Cancel 'Perform some action.
Keyword = ""
MsgBox "User Cancel", vbInformation, "Canceled default
Search"
End If
Else
Response2 = MsgBox(msg2, Style2, Title2)
Keyword = ""
Keyword.SetFocus
End If


'3075 Please enter a valid Keyword



End Sub

 

[Jamie Collins]

Since we're
talking about Access and, say, SQL Server as a backend, what software do
you use for doing a middleware sort? Excel ?

'Access' means different things to different people.

If you are asking what middleware would I use in VBA code for SQL
Server data, my answer would be ADO and I would sort a resultset using
the Recordset object's Sort method (assuming there was no compelling
reason to do so in the SQL code).

If by 'Access' you mean anything other than Jet or VBA then you are
asking the wrong person ;-)

Jamie.

--

 

[aaron.kempf]

Access Data Projects are the MOST POPULAR FORMAT under FILE, NEW..

it is time like you kids stop treating them like a minority.

-Aaron

 

[punjab_tom]

just use SQL for sorting.. what's the big deal?

Excel isn't a tool; when you need to sort in excel you need 2 copies of
the same date.

I think that all of the people in the world; spending half of their
efforts in Excel has a total worldwide value of ZERO dollars.

-Tom

 

[James A. Fortune]

just use SQL for sorting.. what's the big deal?

Excel isn't a tool; when you need to sort in excel you need 2 copies of
the same date.

I think that all of the people in the world; spending half of their
efforts in Excel has a total worldwide value of ZERO dollars.

-Tom

In the context of SQL Server I think that middle tier is often done with
a .NET language such as C# or VB. As another poster once pointed out,
Access can act like a self-contained three tier system. I was just a
little surprised to hear a reference to Access in the context of a
middle tier. The driving force behind three tier is that management at
some companies felt they were losing control of their primary business
information assets to IT people. By having a place where they could
decide the "business rules" in a way they could relate to, managers felt
like they were regaining some sense of control. I think SOA is the
newest method for achieving that end in addition to its promise of code
reuse.

Excel has its place, but for me it has had the effect of giving
management cool spreadsheet ideas that they'd like to see Access mimic.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

In the context of SQL Server I think that middle tier is often done with
a .NET language such as C# or VB.

I think I used the term 'middleware' (not 'teirs') to be intentionally
vague but what I had in mind were data access components such as ADO
and DAO.

Access can act like a self-contained three tier system.

"Self-contained three tier system"? Don't you think that's a little

I think SOA is the
newest method for achieving that end in addition to its promise of code
reuse.

I'm looking at Windows Workflow Foundation just now.

Excel has its place, but for me it has had the effect of giving
management cool spreadsheet ideas that they'd like to see Access mimic.

I recommend you encourage good practice by users of Excel (and other
external data access tools) by creating SQL VIEWs and helper SQL
PROCEDUREs (i.e. to control INSERT, UPDATE and DELETE operations) ,
being ANSI mode neutral (e.g. supporting both * and % flavours of
wildcard, therefore supporting DAO and ADO), strong engine-level data
integrity, judicious use of permissions on objects, etc.

Jamie.

--

 

[punjab_tom]

Jamie;

supporting DAO and * is not a FEATURE; it is a BUG

-Aaron

 

[aaron.kempf]

I'll choose ADP clientserver over unmanagable MDB files -- any day of
the week.

MDB is a 3rd tier.. queries can offer some abstraction layer.
of course; so can views and sprocs.. without any added complexity (MDB
needs connection strings and compact and repair and SQL passthrough and
DAO _AND_ ADO)

-Aaron

 

[James A. Fortune]

I think I used the term 'middleware' (not 'teirs') to be intentionally
vague but what I had in mind were data access components such as ADO
and DAO.

You succeeded . The data access components possibility reminded me of:

"Self-contained three tier system"? Don't you think that's a little
paradoxical <g>?

Here is the post I was talking about:

http://groups.google.com/group/microsoft.public.access/msg/ef683f58ed6ddff6?hl=en&

With its data access components, Access can act as a middle tier
connected to SQL Server while simultaneously acting as another tier.
But I would consider that setup as more of a feasibility study for
something more formal.

I'm looking at Windows Workflow Foundation just now.

I think Microsoft has alread missed the boat for the purpose I
mentioned, at least for the largest corporations. I've looked into
Windows Workflow Foundation (WWF). It was a little disheartening to see
that Microsoft's best UI improvements were in an area of potential
future competition with me, but I'll try, if possible, to find a way to
use their UI with my work so that I'm not wrestling against a great
interface. Back in the Access 2.0 days, workflow was a set of
checkboxes in a table that sent the record on to the next processing
form. I don't know if WWF will integrate with Access 2007.

I recommend you encourage good practice by users of Excel (and other
external data access tools) by creating SQL VIEWs and helper SQL
PROCEDUREs (i.e. to control INSERT, UPDATE and DELETE operations) ,
being ANSI mode neutral (e.g. supporting both * and % flavours of
wildcard, therefore supporting DAO and ADO), strong engine-level data
integrity, judicious use of permissions on objects, etc.

I let everyone in the office know that if they create an Excel worksheet
to accomplish something, they are likely on their own. I'll pull data
in from Excel or create a .xls data or graph file from Access using a
template if I must, but I don't let users mix Excel and SQL Server
together. If Access is an "external data access tool," what other steps
would need to be followed in order to draw the line between the Access
"client-side" operations and the SQL Server server-side operations? I
assume that CHECK constraints (server-side) are preferable to Access'
Validation Rules? I missed most of the possibilities of using CHECK
constaints before because MS made those possibilities implicit rather
than explicit. I.e., you had to take steps to discover them rather than
coming across them naturally within Access.

James A. Fortune
(e-mail address removed)