Sort SQL

[rebelscum0000]

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

 

[Jeff L]

What are you trying to do? If you are just trying to view the data,
you would be better off making and saving a query in the database
window. Then use the OpenQuery to run the query and see the data.
Open Query requires a pre-existing query.

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

 

[John Vinson]

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

Are you trying to sort the TABLE?

If so, don't bother. Tables *have no order* in any usable sense.
They're unordered "bags" of records.

If you want to see records in a particular order, you must - no
options - use a Query based on the table, with an Order By clause in
the query.

Such a query can be used as the basis for a Form, a Report, an Export,
pretty much anything you can do with a table.

John W. Vinson[MVP]

 

[rebelscum0000]

What are you trying to do? If you are just trying to view the data,
you would be better off making and saving a query in the database
window. Then use the OpenQuery to run the query and see the data.
Open Query requires a pre-existing query.

Dear Jeff,

I am not trying to view the data, ,
I have a Table called MyKeywords.Tbl, Whit only one Field Name Called
MyKeyword which is growing each time, when the code INSERTs INTO a new
register (SQL5 Statement) ,
They way I enter the Keywords is not in order, I would like the
strSelectSQL Statement
order them in an Ascending way

In Other words I enter the keywords for example as follows:
Norton
Abacus
WinZip

I need the strSelectSQL Statement order them as follows:
Abacus
Norton
WinZip

Without Maaking and saving a query in my database, is this possible?

Thank in Advance

Regards,
Antonio Macias

 

[punjab_tom]

you shouldn't be scared of 'making queries and saving them'

queries are your friends

-Tom

 

[rebelscum0000]

Thanks to all for your help

but I am still lost

I did some modifications and now I will use like Tom said Queries as My
Friends

I have created a new Query called OrderMyKeywords_Query

SELECT MyKeywords_Tbl.ID, MyKeywords_Tbl.MyKeyword
FROM MyKeywords_Tbl
ORDER BY MyKeywords_Tbl.MyKeyword;

My data is in an Ascending way, No problem here,
Now How to Move back my new data From the Query To the Table
MyKeywords.Tbl Without create dups?

Thanks in Advance
Regards,
Antonio Macias

 

[John Vinson]

My data is in an Ascending way, No problem here,
Now How to Move back my new data From the Query To the Table
MyKeywords.Tbl Without create dups?

You don't.

Tables are used to STORE DATA.

Queries are used to SELECT, SORT, AND ARRANGE DATA.

You should not *care* what order the records are in the Table. The
query didn't "move data" out of the table, and you don't need to "move
data" back.

The query is just a way of viewing the data, in a particular sort
order in this case.

Could you explain *why* you feel that you need to "sort the table" or
"move the data back"?????

John W. Vinson[MVP]

 

[rebelscum0000]

Dear John,
Just because I had the no sense idea that sorting a table, having all
the records in order in all my tables, my database will become more
organized, and functional

Thank you very much for help me to understand my error

Regards,
Antonio Macias

 

[John Vinson]

Dear John,
Just because I had the no sense idea that sorting a table, having all
the records in order in all my tables, my database will become more
organized, and functional

Thank you very much for help me to understand my error

Regards,
Antonio Macias

It's hard to grasp until you get used to it! (And apologies for
"yelling" at you).

What will make your database more organized and functional is to have
all of the tables properly normalized, and to apply proper indexes to
the fields in the table. As a rough rule, any field used to sort or
search a table should have an Index; if the field value should occur
once and once only in the table, it should be a Unique Index.

John W. Vinson[MVP]

 

[Van T. Dinh]

See my comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

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)

[VTD] Well-done ... The best way to learn is to do your own investigations
....

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"

[VTD] Yep. RunSQL (similar to Execute) can only run "Action" SQL, e.g.
Insert, Update, Delete and not a Select SQL.

-----> 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;.'

[VTD] The OpenQuery method expects a save Query Name and not an SQL String.

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

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

[VTD] Dim db_Keyword As Database

is fine. Only the DAO Library has the Database object so there is no
ambiguity here (unlike the Recordset object which exists in DAO Library and
ADO Library - DAO Recordsets and ADO Recordsets are not compatible and hence
the full reference like

Dim rst As DAO.Recordset

is preferred than simply:

Dim rst As Recordset

). Wether the full reference is actually required or not depends on the
References in your database and your code.

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

[VTD] In the context of the code you posted above, Execute is the DAO's
method and RunSQL is the Access' method. Basically, both Execute and RunSQL
performs the same function but they come from different components of what
we commonly know as "Access" software. Most programmers prefer the Execute
with dbFailOnError as this method provides trappable errors which can be
handled by (error-trapping) code. Note also that RunSQL is only available
in Access (Access VBA) while the DAO Library can be added to other
applications (e.g. VB 6, Excel ...) to access / use a JET Back-End so
Execute is available in "more" applications.

<the rest snipped>

 

[Rick Brandt]

There's a few contradictions here.

A *logical* table is a set. Sets have no order by definition.

A *physical* table has an order, as determined by the physical
storage. Computers are notoriously bad when it comes to randomness
and you will get the same order each time, unless something changes
in the interval. The sort order on disk, and therefore the 'default'
order when no order is explicitly requested, can be specified by
adding a PRIMARY KEY to the table and compacting the file.

I too choose to disregard such physical characteristic of the mdb file
format and stick to the logical considerations. So I agree, tables
have no order.

Now, if by 'Query' you mean a stored Query object based on a
SELECT..FROM query, you would be talking about a VIEW or virtual
table. A virtual table should behave as a materialized table,
therefore logically a virtual table also has no order. Yet you seem
to be recommending putting an ORDER BY clause in a stored query
object, being a physical object.

In summary, you say a physical table should be considered logically
with no order and but that a virtual table should be physically stored
in such a way that it will have the effect of an inherent order!

Try something like this in ANSI-92 Query Mode (e.g. via an ADO
connection) on a Jet 4.0 database (e.g. Northwind):

CREATE VIEW DropView
AS
SELECT CompanyName
FROM Customers
ORDER BY CompanyName;

You get an error, 'Only simple SELECT queries are allowed in VIEWS,'
which sounds strange considering the trivial nature of the query but
what it is trying to tell you is that a VIEW (a virtual table) cannot
have an order. OK, you can do some 'physical' manipulation in the
Access UI to force a sort order but is this good practice?

Jamie.

A stored Access query is *similar* to a VIEW, but that does not make it the
*same* as a VIEW. If the query is to be used in a process where a specific
order is required (or desired) then an order by clause should be put in the
query.

 

[Douglas J. Steele]

Now, if by 'Query' you mean a stored Query object based on a
SELECT..FROM query, you would be talking about a VIEW or virtual table.
A virtual table should behave as a materialized table, therefore
logically a virtual table also has no order. Yet you seem to be
recommending putting an ORDER BY clause in a stored query object, being
a physical object.

What's the contradiction? Just because you equate queries to views doesn't
mean they are the same.

Of course you can include ORDER BY clauses in stored queries in Access.

 

[Rick Brandt]

What about a base table? Is an Access base table the same as or merely
*similar* to a base table as described in the SQL standard?

I think an Access base table has a physical order by I prefer to think
logically and consider an Access base table the same as a standard SQL
table.

Jamie.

A saved query is not a table. Nor is it a View. It is what it is and
appropriate use is dictated by what it actually is, not what you would like
to treat it as.

 

[rebelscum0000]

Dear Gurus,

I Started this Topic asking a no sense idea that I had,
well I have another for you

The true is I am learning, I am newbie, but with all these replies
I think I will become very soon a Guru like you

My new topic is MAKE A NEW TABLE SQL
AT
http://groups.google.com.mx/group/m...f322b/dc4c0446269fe5a8?hl=en#dc4c0446269fe5a8

Could you please help me again?

Thanks in advance

Regards,
Antonio Macias

 

[James A. Fortune]

What's the contradiction? Just because you equate queries to views doesn't
mean they are the same.

Terminology could be an issue here. Some definitions follow:

· "A table is either a base table, a viewed table, or a derived
table." (SQL-92 standard).

· A 'base table' is a persistent object (none of the other
definitions in the SQL-92 standard apply to Access/Jet).

· A 'view' is a virtual table defined by a query expression, as
described in the SQL-92 standard (which calls it a 'viewed table').

· An 'Access table' is a Table object as implemented in Access/Jet
SQL.

· A 'cursor' is "a data structure that describes the results returned
from a SQL SELECT statement."
(http://www.orafaq.com/glossary/faqglosc.htm), as distinct from a
'caret', being that blinking character indicating the insertion point
etc.

· The Information Schema described in the SQL-92 standard and
describes the persistent objects in the schema (for Access/Jet's
implementation, see: ADO 2.8 API Reference: SchemaEnum:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp).

A 'table' is a set. A set has no concept of order. Therefore, a 'base
table' has no inherent order and a 'virtual table' has no inherent
order. Consider the SQL term INSERT which adds a rows but with no
implication of their position in the set.

An 'Access table' has a physical order, determined by a number of
factors (PRIMARY KEY designation, date/time order of insertion of rows,
when the file was compacted, etc) and, provided none of those factors
change, the inherent order will be the same. Consider the Access term
'Append query', which implies the rows will be added at the every end,
which is exactly what happens with an Access table.

Of course you can include ORDER BY clauses in stored queries in Access.

A Jet SQL VIEW is an Access Query object, a fact that can be discovered
by executing a CREATE VIEW statement then opening the mdb in the Access
UI and examining the Query. So an Access Query object can be a 'virtual
table'.

A Jet SQL VIEW cannot have an ORDER BY clause; attempting to execute a
CREATE VIEW statement using a query expression that has an ORDER BY
clause results in an error (**see below).

I recognise that an Access Query object is not always a VIEW or
'virtual table'. Sometimes it contains a SQL DML statement (INSERT,
UPDATE, DELETE) as distinct from a SQL DML query (small q), being a
SELECT expression.

[Confusingly, the Access community refers to SQL DML statements as
'queries', hence terms such as 'delete query' are oxymoron to the wider
SQL community.]

A Query object can even contain SQL DDL and/or SQL DCL expressions e.g.
the following is valid syntax (yes, three CREATE PROCs in one statement
and yes, the result is worthless):

CREATE PROC Proc1
AS
CREATE PROC Proc1
AS
CREATE PROC Proc1
AS
BEGIN TRANSACTION;

So an Access Query objects can contain all kinds of funky things,
including a SELECT query with an ORDER BY clause.

My point: it's fine by me if you want to consider the physical
implementation aspects of objects such as 'table' and 'virtual' table,
in which case an Access table has an inherent order and an Access
Query-As-Virtual-Table (lacking a term here) may or may not have an
explicit order. I'd rather they were considered in pure SQL terms and
say that neither have inherent ordering (but then I am a 'purist'
myself <g>). What I would view as contradictory is picking and choosing
i.e. saying a 'table' has no order but a 'virtual table' can have an
order.

** from above:

Actually, that's not quite true. You can do this:

CREATE PROC DropView
AS
SELECT CompanyName
FROM Customers
ORDER BY CompanyName;

which will create an Access query object and expose it to the
Information Schema as a VIEW

As is often said round here, that something *can* be done does not mean
it *should* be done.

Jamie.

Jamie,

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. The Celko thread you cite has posters
giving reasons of parallelism and efficiency as well as implementational
problems that orders in views bring. This means that a view is purely a
virtual table. Access programmers often use stacked queries. If a
query without order is used as the input of another query, how does the
input query differ from a view? If I can get all the data I need from a
view, should I apply a query at the end just to do the "ORDER BY?" I
may have missed your point due to my Access perspective of things. If
so, please clarify, supplying new terminology if necessary .

James A. Fortune
(e-mail address removed)

 

[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

 

[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)