Sort Question

[Guest]

I have some code that creates a recordset using SQL including an Order By
Clause
and then writes the data from that recordset to a table. The whole purpose
of this code is to create a table that is sorted the specific way I want. I
could do this using a query but I am trying to do everything in this DB
without using any queries.

When I run the procedure it runs fine but theresulting table is not sorted.
I have tried the Order By clause as well as the sort property and neither
seems to make a difference. I'm sure it's something simple that I've missed
but I can't see it.

Here's the code:

Public Sub FillReportTable()


Dim Db As DAO.Database
Dim rst1 As DAO.Recordset
Dim tdf As TableDef
Dim rst2 As Recordset

Set Db = CurrentDb()

Set rst1 = Db.OpenRecordset("Select * From Comparisons ORDER BY [Project],
[SeqNum];")
Set tdf = Db.TableDefs("Report")
Set rst2 = tdf.OpenRecordset(dbOpenDynaset)

With rst1
If .RecordCount = 0 Then
MsgBox "There's a serious problem"
Exit Sub
End If
.MoveFirst
With rst2
Do While Not rst1.EOF
rst2.AddNew
rst2![Project] = rst1![Project]
rst2![Project Manager] = rst1![Project Manager]
rst2![Actual Labor Units] = rst1![Actual Labor Units]
rst2![Planned Labor Units] = rst1![Planned Labor Units]
rst2![At Completion Labor Units] = rst1![At Completion Labor Units]
rst2![Actual Duration] = rst1![Actual Duration]
rst2![Planned Duration] = rst1![Planned Duration]
rst2![At Completion Duration] = rst1![At Completion Duration]
rst2![Budget Consumed] = rst1![Budget Consumed]
rst2![Effort % Complete] = rst1![Effort % Complete]
rst2![Remaining Effort] = rst1![Remaining Effort]
rst2![Remaining Duration] = rst1![Remaining Duration]
rst2![Committed Elevation Date] = rst1![Committed Elevation Date]
rst2![DataDate] = rst1![DataDate]
rst2![SeqNum] = rst1![SeqNum]
rst2![Duration % Complete] = rst1![Duration % Complete]
rst2![Progress Alert] = rst1![Progress Alert]
rst2![Daily Effort Required] = rst1![Daily Effort Required]
rst2![Average Actual Daily Effort] = rst1![Average Actual Daily Effort]
rst2![Schedule Alert] = rst1![Schedule Alert]
rst2![Duration Variance] = rst1![Duration Variance]
rst2![Effort Variance] = rst1![Effort Variance]
rst2.Update
rst1.MoveNext
Loop
End With
End With
End Sub

TIA,

 

[David C. Holley]

What is the specific reason why you need the data SORTED in the table?
BTW its probably not sorted they way you desire because of varous
indexes you've set up.

 

[Guest]

Once I have the data in the table I export it as is to an Excel Spreadsheet.
The spreadsheet formats the data and presents it to the user as a "report".
This report is an iterative comparison of the data I provide comparing this
week to last week. So the sort I am creating is basically sorting by project
name and then iteration so that the data is presented to the end user in a
manner that makes sense to them.

Thanks,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

What is the specific reason why you need the data SORTED in the table?
BTW its probably not sorted they way you desire because of varous
indexes you've set up.

I have some code that creates a recordset using SQL including an Order By
Clause
and then writes the data from that recordset to a table. The whole purpose
of this code is to create a table that is sorted the specific way I want. I
could do this using a query but I am trying to do everything in this DB
without using any queries.

When I run the procedure it runs fine but theresulting table is not sorted.
I have tried the Order By clause as well as the sort property and neither
seems to make a difference. I'm sure it's something simple that I've missed
but I can't see it.

Here's the code:

Public Sub FillReportTable()


Dim Db As DAO.Database
Dim rst1 As DAO.Recordset
Dim tdf As TableDef
Dim rst2 As Recordset

Set Db = CurrentDb()

Set rst1 = Db.OpenRecordset("Select * From Comparisons ORDER BY [Project],
[SeqNum];")
Set tdf = Db.TableDefs("Report")
Set rst2 = tdf.OpenRecordset(dbOpenDynaset)

With rst1
If .RecordCount = 0 Then
MsgBox "There's a serious problem"
Exit Sub
End If
.MoveFirst
With rst2
Do While Not rst1.EOF
rst2.AddNew
rst2![Project] = rst1![Project]
rst2![Project Manager] = rst1![Project Manager]
rst2![Actual Labor Units] = rst1![Actual Labor Units]
rst2![Planned Labor Units] = rst1![Planned Labor Units]
rst2![At Completion Labor Units] = rst1![At Completion Labor Units]
rst2![Actual Duration] = rst1![Actual Duration]
rst2![Planned Duration] = rst1![Planned Duration]
rst2![At Completion Duration] = rst1![At Completion Duration]
rst2![Budget Consumed] = rst1![Budget Consumed]
rst2![Effort % Complete] = rst1![Effort % Complete]
rst2![Remaining Effort] = rst1![Remaining Effort]
rst2![Remaining Duration] = rst1![Remaining Duration]
rst2![Committed Elevation Date] = rst1![Committed Elevation Date]
rst2![DataDate] = rst1![DataDate]
rst2![SeqNum] = rst1![SeqNum]
rst2![Duration % Complete] = rst1![Duration % Complete]
rst2![Progress Alert] = rst1![Progress Alert]
rst2![Daily Effort Required] = rst1![Daily Effort Required]
rst2![Average Actual Daily Effort] = rst1![Average Actual Daily Effort]
rst2![Schedule Alert] = rst1![Schedule Alert]
rst2![Duration Variance] = rst1![Duration Variance]
rst2![Effort Variance] = rst1![Effort Variance]
rst2.Update
rst1.MoveNext
Loop
End With
End With
End Sub

TIA,

 

[Marshall Barton]

I have some code that creates a recordset using SQL including an Order By
Clause
and then writes the data from that recordset to a table. The whole purpose
of this code is to create a table that is sorted the specific way I want. I
could do this using a query but I am trying to do everything in this DB
without using any queries.

When I run the procedure it runs fine but theresulting table is not sorted.
I have tried the Order By clause as well as the sort property and neither
seems to make a difference. I'm sure it's something simple that I've missed
but I can't see it.

Here's the code:

[snip the code]


Apparently there are some serious misperceptions here.

First, the records in a table are not sorted in any order
(at least not one that is useful to you). That obviates the
need for this procedure (at least from what you've said so
far about why you are doing this). If you want to view the
data in a table sorted in some order, then you **must** use
a query to do the sorting.

Second, you said you can do this with a query, but don't
want to use a query. Can you explain why? A query is far
more efficient and easier to use than all that code.

 

[Guest]

The only reason I have not to write a query is that I have users who know how
to see, modify and as a result break queries. They don't know how to destroy
code. If I weren't relatively new to Access development and development in
general, I'm sure I would handle this differently by using permissions or
securing the database some way.

Thanks,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

I have some code that creates a recordset using SQL including an Order By
Clause
and then writes the data from that recordset to a table. The whole purpose
of this code is to create a table that is sorted the specific way I want. I
could do this using a query but I am trying to do everything in this DB
without using any queries.

When I run the procedure it runs fine but theresulting table is not sorted.
I have tried the Order By clause as well as the sort property and neither
seems to make a difference. I'm sure it's something simple that I've missed
but I can't see it.

Here's the code:

[snip the code]


Apparently there are some serious misperceptions here.

First, the records in a table are not sorted in any order
(at least not one that is useful to you). That obviates the
need for this procedure (at least from what you've said so
far about why you are doing this). If you want to view the
data in a table sorted in some order, then you **must** use
a query to do the sorting.

Second, you said you can do this with a query, but don't
want to use a query. Can you explain why? A query is far
more efficient and easier to use than all that code.

 

[David C. Holley]

So why not just export the QUERY to Excel directly? Why create a table
out of the data?

 

[David C. Holley]

If they know how to break a query, they aren't that far away from
changing code. If they CHOOSE to do so, they CHOOSE to do so. If you
want to prevent changes to objects then you need to implement security.
That is why security is there.

1. Implement Security
2. Use a query to output the data.

 

[Guest]

Ok, I get the message but I'm still interested in the original problem from a
purely academic point of view. What is in fact happening with my sorts? The
way I would expect the procedure to behave is for Access to write the records
to the table in the order they are read from the other recordset. So if the
other recordset is sorted...

Thanks for your pointers, I'm not trying to beat a dead horse just trying to
get a better understanding of how this all works. If someone wants to point
me to some valuable training and or literature I'm game!

 

[Marshall Barton]

Ok, I get the message but I'm still interested in the original problem from a
purely academic point of view. What is in fact happening with my sorts? The
way I would expect the procedure to behave is for Access to write the records
to the table in the order they are read from the other recordset. So if the
other recordset is sorted...

Thanks for your pointers, I'm not trying to beat a dead horse just trying to
get a better understanding of how this all works. If someone wants to point
me to some valuable training and or literature I'm game!

As I said before:

The Records in a table are not
stored in any particular order!

and

If you want to retrieve the records
in a specified order, you **MUST**
use a query with an Order By clause.

Note that you can use VBA code to create a query on the fly.
See CreateQueryDef in Help. You should automate the export
operation behind a button anyway so the users shouldn't
event know the name of the query you're using.

Maybe all you need to do is hide your query so the users
have to take an extra step to find it. You can use the
Tools - Options menu to hide system objects and you can
include your own objects in that by using USYS as the first
4 characters in their name.

 

[David C. Holley]

The question being asked is *WHY* aren't the records inserted into the
table in the order in which they are appear in the query.

 

[Marshall Barton]

I didn't read the question that way, but the answer to that
is the records are stored on disk in whatever way Access (or
its programmers) finds convenient. Relational database
theory is quite clear on not imposing rules about how data
is stored so that database system developers can be free to
optimize saving data to the system's advantage.

The same applies to retrieving records. The records are
only sorted by a query with an Order By clause after the
data has been retrived.

 

[Guest]

You have both been very helpful if for no other reason than pointing out that
I've got a lot to learn. Thanks for your help on this, I truly appreciate it.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

I didn't read the question that way, but the answer to that
is the records are stored on disk in whatever way Access (or
its programmers) finds convenient. Relational database
theory is quite clear on not imposing rules about how data
is stored so that database system developers can be free to
optimize saving data to the system's advantage.

The same applies to retrieving records. The records are
only sorted by a query with an Order By clause after the
data has been retrived.
--
Marsh
MVP [MS Access]

The question being asked is *WHY* aren't the records inserted into the
table in the order in which they are appear in the query.

 

[David C. Holley]

So then there are two dimensions at work - the first being the physical
organization of the records, which is what is being used when the
records are put into the new, temporary table and the second which is a
virtual organziation based on the specified ORDER BY statements.

 

[Guest]

I think what I am understanding from this is that the records may and
probably are being written in the order they are read (therefore sorted) but
when I open the table for viewing and or exporting, the order they are
displayed has nothing to do with the order they are physically stored in the
table. This actually makes sense to me on one level. It's not that my sorts
aren't working, if I did something like a debug.print after my sort, the
records would print in the right order.

I will take the advice I've received and secure the database and I will also
use the createquerydef method to pass to the export to excel. Do you know
whether I can use a querydef I've created on the fly like this to pass to a
transferspreadsheet method?

Thanks,
m

 

[Marshall Barton]

I think what I am understanding from this is that the records may and
probably are being written in the order they are read (therefore sorted) but
when I open the table for viewing and or exporting, the order they are
displayed has nothing to do with the order they are physically stored in the
table. This actually makes sense to me on one level. It's not that my sorts
aren't working, if I did something like a debug.print after my sort, the
records would print in the right order.

Yes, that's essentially correct provided that you understand
the key work in there is "probably". There is no
requirement they be written that way, especially if it the
developers decide to reuse space vacated by edited/deleted
records.

It may also help your thinking to know that viewing a table
in sheet view (i.e. opening a table from the Database
Window) is just asking Access to open a very simple form
using a very basic query.

I will take the advice I've received and secure the database and I will also
use the createquerydef method to pass to the export to excel. Do you know
whether I can use a querydef I've created on the fly like this to pass to a
transferspreadsheet method?

Sure you can. Just remember to Refresh the QueryDefs
collection after you add/edit the querydef. Speaking of
editing an existing querydef, you could set an existing
querydef's SQL property to avoid deleting/recreating objects
all the time. This will reduce both the bloating issue and
the chances for corruption.

 

[Guest]

Thanks All!