Sort table, not with SQL!

[Daron]

How do I sort a table, from a form. I can't use SQL!

WE have an Access application that is created by an outside vendor. The
one form states: "Please make sure all records in table are sorted by
date..."

The application is not locked or hidden in any way, but is fairly
involved. I would like to be able to simply add a button to this form
to sort the table.

I've tried playing around with OrderBy, but cann't seem to get the
correct syntax. Any help would be greatly appreciated!

===== My Code =====
Dim tdf As TableDef
Dim db As Database

Set db = CurrentData()

Set tdf = db.TableDefs("ImportProgramServicePeriods")
tdf.OrderBy = "ValBeginningDate, ASC"

[Randy Harris]

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

How do I sort a table, from a form. I can't use SQL!

WE have an Access application that is created by an outside vendor. The
one form states: "Please make sure all records in table are sorted by
date..."

The application is not locked or hidden in any way, but is fairly
involved. I would like to be able to simply add a button to this form
to sort the table.

I've tried playing around with OrderBy, but cann't seem to get the
correct syntax. Any help would be greatly appreciated!

===== My Code =====
Dim tdf As TableDef
Dim db As Database

Set db = CurrentData()

Set tdf = db.TableDefs("ImportProgramServicePeriods")
tdf.OrderBy = "ValBeginningDate, ASC"

There is no such thing as sorting a table. That's the nature of a relational
database. You can easily view the content of a table in sorted order by
using a query. You can view the data in sorted order in a form or a report.

[Daron]

To quote from above:

How do I sort a table, from a form. * I can't use SQL!* ....

If I have the table open, I can select the column/field, and sort from
the Records/Sort... menu. This is what I need to replicate in code.

I do not have control over the original code, and we do receive updates
occasionaly.

I have been programming in Access for over 7 years, and am fully aware
that the preferred way to sort is with a query. HOWEVER, this is not my
app.

Having a simple button and a couple of lines of code to sort the table
is my only real option. I can then simply copy and paste when we get an
update.

Daron

[Rick Brandt]

Daron wrote:

To quote from above:

How do I sort a table, from a form. * I can't use SQL!* ....

If I have the table open, I can select the column/field, and sort from
the Records/Sort... menu. This is what I need to replicate in code.

I do not have control over the original code, and we do receive
updates occasionaly.

I have been programming in Access for over 7 years, and am fully aware
that the preferred way to sort is with a query. HOWEVER, this is not
my app.

Having a simple button and a couple of lines of code to sort the table
is my only real option. I can then simply copy and paste when we get
an update.

Daron

The point is that there is no point to sorting a table because that sort
will not be used anywhere else. Not in forms, queries, or reports based on
that table. It only affects when you actually look directly at the table
datasheet and if someone is going to do that then why not have them sort it
manually at the same time?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Daron]

Lets back up a bit...

From original post:
We have an Access application that is created by an outside vendor. The one form states: "Please make sure all records in table are sorted by
date..."

Please note the part about an OUTSIDE VENDOR, also the message about
the TABLE. This is part of an data append procedure.

from my second post I do not have control over the original code, and we do receive updates

occasionaly.

Rather than this turning into some kind of flame war, we need to look
at the requirements.

It would appear that the OUTSIDE VENDOR is not employing a query to
sort the table before they append. This app is very involved and uses a
large number of procedures and classes. As they will continue to update
this, I do not want to get into rewritting tons of code. I need a quick
and dirty way to make sure the TABLE is sorted.

Unless you can help by showing me some code to SORT THE TABLE, please
don't respond.

I do greatly appreciate the time that has been used in responding to my
request, but the blindspot on using a query is a roadblock at this
point.
-Daron

[pietlinden]

Umm... go to the vendor and tell him what you want?

[Salad]

Daron wrote:

How do I sort a table, from a form. I can't use SQL!

WE have an Access application that is created by an outside vendor. The
one form states: "Please make sure all records in table are sorted by
date..."

The application is not locked or hidden in any way, but is fairly
involved. I would like to be able to simply add a button to this form
to sort the table.

I've tried playing around with OrderBy, but cann't seem to get the
correct syntax. Any help would be greatly appreciated!

===== My Code =====
Dim tdf As TableDef
Dim db As Database

Set db = CurrentData()

Set tdf = db.TableDefs("ImportProgramServicePeriods")
tdf.OrderBy = "ValBeginningDate, ASC"

Sub SortIt()
Dim rst As Recordset
Dim rstSort As Recordset

'open the table
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)

'set the sort to descending.
rst.Sort = "ID Desc"

'for grins, set a filter to show id's greater 123
rst.Filter = "ID > 123"

'now create a clone with the sort.
Set rstSort = rst.OpenRecordset

rstSort.MoveFirst
MsgBox rstSort!ID

rstSort.Close
rst.Close
Set rstSort = Nothing
Set rst = Nothing

End Sub

[Rick Brandt]

Daron wrote:

Lets back up a bit...

From original post:
We have an Access application that is created by an outside vendor.
The

one form states: "Please make sure all records in table are sorted by
date..."

Please note the part about an OUTSIDE VENDOR, also the message about
the TABLE. This is part of an data append procedure.

from my second post

I do not have control over the original code, and we do receive
updates occasionaly.

Rather than this turning into some kind of flame war, we need to look
at the requirements.

There have been no flames and no responses about whether this is a good practice
or not. What you have been told is that "sorting a table" is a meaningless
concept. It would be the same as if a disk defrag program asked you to open
Windows Explorer and sort the files by name before running the defrag. That
would make just as much sense as asking a user to sort a table before an append.

The only thing that comes close to sorting a table is to compact the database
which will put the table in primary key order on the disk.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

"Daron" <Da**********@gmail.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

Unless you can help by showing me some code to SORT THE TABLE,
please don't respond.

Then nobody is going to respond.

You're asking an impossible question and putting requirements on the
answer that eliminate the only workable answer.

Why ask a question if you're not going to accept the answer?

In any event, you haven't provided enough information about the
context where you want to sort to be able to answer your question.
Sorting the default datasheet view of a table has no effect on any
forms or reports that present the data. You can only change those
forms order of presentation by either changing the ORDER BY
properties of those forms 9or adding sorting/grouping in a report)
or adding an ORDER BY clause to the recordsources.

From your description, it doesn't sound like you have the code
available to you, or that you'd have to update it each time you get
the updated application.

So, there isn't any answer to your question in its present form.

And the only way to solve your problem is to use methods you've
already categorically rejected.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Daron]

Sorry if I am being a bit obtuse these days.

When I search the help files, and look at the "OrderBy Property" entry,
I see this:

"You can use the OrderBy property to specify how you want to sort
records in a form, query, report, or table."

This would make it appear that it is possible to set this property for
a table, or maybe I am not understanding the context. Either way, if
the help file states this is possible for a table, then there should be
a way to do this, correct?

My original post had included my attempted code:

===== My Code =====
Dim tdf As TableDef
Dim db As Database
Set db = CurrentData()
Set tdf = db.TableDefs("ImportProgramServicePeriods")
tdf.OrderBy = "ValBeginningDate, ASC"
also the note : The application is not locked or hidden in any way, but is fairly
involved. I would like to be able to simply add a button to this form
to sort the table.

This would appear to be the context (a button on a form) that I
require.

This data is not going to be used in a form or report, but as I stated,
in a multi-step append procedure. If I wrote the code, of course I
would have sorted it in the procedure, but I didn't write this code.
The sorted table seems to be a requirements from the vendor

This feature has been requested of the vendor, but we have not seen it,
yet.

I really am not trying to be a pain in the arse, but trying to
understand how to perform an action within Access that would appear to
be doable. Since there is a *menu* item, as well as a help file entry,
I would also assume that there is a way to replicate this action from
code. This is all I am really asking to do.

- Daron

[Daron]

Salad,

Thanks for the reply! I will give it a try when I get into work, and
post back.

- Daron

[lylefair]

Well, I certainly won't respond. Hell, I don't even know what SORT THE
TABLE means.
Also, I won't comment that it seems really strange that someone who
has been programming Access for seven years can seem to be so genuinely
ignorant about JET and its characteristics.
Nor will I wonder aloud why a developer with such extensive experience
has contributed so little to this newsgroup prior to coming here with
his ONE BIG QUESTION.
Nor, as to the OP's capability of explaining what he/she means clearly,
will I say a thing.
Because when someone posts here we give him carte-blanche in specifying
who may answer and what he/she may say, and we always follow his/her
directives.
You see this group is not about sharing ideas, skills and routines;
it's just about giving quick and dirty answers to nonsensical
questions.
After all, why would someone who had posted many thousands of answers
here not just hunker down all humble when some newbie comes along to
give direction.
I won't rock the boat; I won't say a word.
And, unless you can help by showing some code to SORT THE TABLE ,
please,don't respond.

[lylefair]

A RecordSet is a Table? I never knew that.

[Randy Harris]

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Sorry if I am being a bit obtuse these days.

I wouldn't say you've been obtuse. You ask for help then threaten a
"flamewar" and insist that no one respond unless they supply you with a
wrong answer that you are certain is correct. Pig headed and rude, yes, but
not obtuse.

[Larry Linson]

"Daron" wrote

Unless you can help by showing me some
code to SORT THE TABLE, please
don't respond.

I will be following Lyle's example and not responding to your question
because I am quite certain I recall that "Relational tables, by definition,
are unordered." and I have always understood that "unordered" meant that the
records cannot be assumed to be in any particular order, which they would
have to be if the table were sorted, or sortable.

And, since setting the OrderBy property of a Table does not appear to affect
even the Datasheet View of that Table, whether or not the Table has a
Primary Key, I'd have to think that property may not exactly mean what it
appears to mean in the quoted Help.

Larry Linson
Microsoft Access MVP

[Daron]

I found a solution to sorting a table!

1) Create a query that will sort the table as needed.
SELECT * FROM tbl_MyTable ORDER BY tbl_MyTable.ValBeginningDate;

I saved this as _qsrt_MySort

2) Put this code in behind your button:
===== Start Code =====

Dim str_Table As String

str_Table = "tbl_MyTable"

Application.Echo False

DoCmd.OpenTable str_Table
DoCmd.ApplyFilter "_qsrt_MySort"
DoCmd.Save acTable, str_Table
DoCmd.Close acTable, str_Table

Application.Echo True

===== End Code =====

This work on repeated attempts.

I hope that this will help someone in the future.

- Daron

[Rick Brandt]

Daron wrote:

I found a solution to sorting a table!

[snip]

And it will still have zero impact on any update procedure that will be run
afterwards.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Salad]

Daron wrote:

Sorry if I am being a bit obtuse these days.

When I search the help files, and look at the "OrderBy Property" entry,
I see this:

"You can use the OrderBy property to specify how you want to sort
records in a form, query, report, or table."

If a form's RECORDSOURCE is the one to be ordered, you can use the
OrderBy property. Ex:
Dim strOrder As String
strOrder = "ID Desc"
Me.OrderBy = strOrder
Me.OrderByOn = True
Simply setting OrderBy to an order does not order the recordsource, you
have to also set OrderByOn.

Although I have not used it, you may also want to try
Docmd.RunCommand acCmdSortAscending
Docmd.RunCommand acCmdSortDescending

You can also try Docmd.DoMenuItem to produce the same things as
RunCommand constants.

[Daron]

If I were to create a table-based record set, I have always thought
that it would explicitly inherit the tables sort order, unless I apply
my own sort to the recordset.

Are you implying that Access would randomly resort the new recordset?
That would make life interesting.

Or am I missing something about the way that Access sorts recordsets?

- Daron

[Randy Harris]

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:N3******************@newssvr25.news.prodigy.n et...

Daron wrote:

I found a solution to sorting a table! [snip]

And it will still have zero impact on any update procedure that will be

run afterwards.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

He still thinks he is sorting the table. He doesn't understand that he is
using a query, which is what he was told in the first place.

[Daron]

"He" has a name.

A query will still inherit the way a table is ordered unless the query
is explicitly sorted.
- Daron

[Rick Brandt]

Daron wrote:

"He" has a name.

A query will still inherit the way a table is ordered unless the query
is explicitly sorted.

Have you even tried a simple test of this? (it's incorrect).

When you open a table in datasheet view and apply a sort you are not
"sorting the table". You are sorting the "view" that Access presents to
you when you look at the table datasheet. It has absolutely no meaning
outside of the context of viewing the table datasheet directly in the GUI.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

"Daron" <Da**********@gmail.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

I found a solution to sorting a table!

1) Create a query that will sort the table as needed.
SELECT * FROM tbl_MyTable ORDER BY tbl_MyTable.ValBeginningDate;

I saved this as _qsrt_MySort

2) Put this code in behind your button:
===== Start Code =====

Dim str_Table As String

str_Table = "tbl_MyTable"

Application.Echo False

DoCmd.OpenTable str_Table
DoCmd.ApplyFilter "_qsrt_MySort"
DoCmd.Save acTable, str_Table
DoCmd.Close acTable, str_Table

Application.Echo True

===== End Code =====

This work on repeated attempts.

I hope that this will help someone in the future.

These are exactly the solutions that you were told on the front end
and that you said were unsatisfactory.

Is it any wonder that you didn't get good answers if you rejected
the answers that you later say are correct?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Daron]

There is a difference between just being told to go fishing, and being
told how to catch the fish.

The query option is a good one, but not a "solution." I also needed to
know a *method* to use the query. Nobody was offering specifics, and as
we all know, the devil is in the details.

Thanks for all the non help, If nothing else, it pushed me to try
alternative ways of accomplishing what I hope the original vendor
needs. They are the ones that state a need to "sort the table."
Frankly, their code has more issues than you even want to know about.

-Daron

PS: I do have the greatest respect for this newsgroup and the people
who contribute to it. The information and code I have found here has
(have?) saved literally hundreds of hours over the years.

David, your stuff has always been of the best quality. I have watched
the attacks that you have had to endure, and you still come up taking
the higher ground. I have come to learn that if you are responding,
then there is going to be something to learn from. I am sorry if this
thread has not gone the way it should.

[Nick Coe \(UK\)]

The only thing I can suggest is to manually create and run
some queries to make a new table filled in the order you
require with a non unique index on the appropriate column.
Then empty the existing table and append the new table's
data back into it (with an ORDER BY just for belt and
braces). But I wouldn't recommend it.

This won't work terribly well, is cumbersome and technically
fails your no SQL caveat. Probably also completely cock up
relationships.

I don't think you're actually going to be able to achieve
your aim, at least not easily.

Why the "Ensure table sorted by date..." on the form? Does
the developer have any rationale or a bit more context to
explain why?

--
Nick Coe (UK)
Available - Will work for money :-)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store

In
news:11**********************@g49g2000cwa.googlegr oups.com,
Daron typed:

How do I sort a table, from a form. I can't use SQL!

WE have an Access application that is created by an
outside
vendor. The one form states: "Please make sure all records
in
table are sorted by date..."

The application is not locked or hidden in any way, but is
fairly involved. I would like to be able to simply add a
button to this form to sort the table.

I've tried playing around with OrderBy, but cann't seem to
get
the correct syntax. Any help would be greatly appreciated!

===== My Code =====
Dim tdf As TableDef
Dim db As Database

Set db = CurrentData()

Set tdf = db.TableDefs("ImportProgramServicePeriods")
tdf.OrderBy = "ValBeginningDate, ASC"

[David W. Fenton]

"Daron" <Da**********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

There is a difference between just being told to go fishing, and
being told how to catch the fish.

The query option is a good one, but not a "solution." I also
needed to know a *method* to use the query. Nobody was offering
specifics, and as we all know, the devil is in the details.

Perhaps nobody offered specifics because you'd rejected the general
principle behind the offered solutions. Why would anyone bother
giving you specifics for a method you'd already explicitly rejectd?

The problem was your rejection, which is what people said to you
repeatedly. The fact that you got no specifics shouldn't be at all
surprising given how adamant you were that the solutions given were
not waht you wanted to do.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[dkintheuk]

I agree with that question.

Why do you (Daron) think you need to sort the table?

There is no need at all to do so. The data in the table is equally
capable of being worked with wether it is stored in neatly ordered rows
(according to your chosen ordering) or if it is just appended as it is
received. As long as you are indexing correctly you will notice NO
BENEFIT AT ALL from the resort.

I am thinking that you might have mistated what the requirement is as
you have not yet give a clear explanation of the reason why you feel it
is necessary.

You've been given a lot of help so far here and i wonder if you're just
being a little over-zealous in determining that people are unhelpful
because of some flippant and quite funny replies that are knocking your
ego a smidge.

If you can give anyone here a good reason to sort the underlying data
in a ANY relational database table in ANY database ever built then we
would have somewhere to go with giving a sensible answer.

Finally, it seems that as soon as you get an update to your code from
your vendor, you are going to lose buttons from your forms, or are they
only working on the back end data; in that case, you will lose any
sorting and will have to constantly resort, but i just can't see aany
good reason why you should.

Hopefully you understand that i am not being flippant or rude or
intending to begin flamewars, i'm just offering so advice from my own
experience and if it helps then that's lovely.

Rob.