How to sort rows in a table programmatically?

[SashaBuilder3]

Hi everybody,

Another newbie problem.

I have a table in an Access db (say, Tbl1) an I need to export SORTED by date rows into a CSV spreadsheet.

What I do is the following (simplified):


1. I sort the table with transfering data into Tbl2 (to avoid circular reference).

SQL_str = "SELECT * INTO Tbl2 FROM Tbl1 ORDER BY MyDate;"
DoCmd.RunSQL (SQL_str)


2. I remove first table
SQL_str = "DROP Table Tbl1;"
DoCmd.RunSQL (SQL_str)

3. I rename the second table into the first.
DoCmd.Rename "Tbl1", acTable, "Tbl2"


4. I export the table as a CSV spreadsheet
DoCmd.TransferText acExportDelim, , "Tbl1", "Tbl1.csv", True


To my undrestanding now the table should contain the soprted by date rows. It works BUT NOT ALWAYS!

Sometimes rows are sorted, the other times not.



Please, help anyone. Any idea what's happening?



Thanks,

Alexandre

 

[KenReay]

Hi

Make a Query in the query builder to give you tble1 ordered by Date

Use DoCmd.TRansferText to export the query

Regards
Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[SashaBuilder3]

Ken,

Thanks for responding.

I didn't tell the whole truth. I actually tried to use a query in the routine, it was my first attempt.


Set db = CurrentDb

SQL_str = "SELECT * FROM Tbl1 ORDER BY SMDTSTR;"

Set myQueryDefine = db.CreateQueryDef("qry_tmp_sort", strSQL)

SQL_str = "SELECT * INTO Tbl2 FROM qry_tmp_sort;"
DoCmd.RunSQL (SQL_str)

etc.


This resulted in the same thing: some of the outputs were sorted, some weren't. Then I removed this query step but little was changed.

Do you think maiking a query and then outputting its contents directly (omitting creation of the second table) will solve the problem?


Anyway, I'll try.


Thanks again,

Alexandre

 

[SashaBuilder3]

Ken,

Seems that your method works.

Thanks a lot!

Alexandre

 

[KenReay]

Hi

To understand why it works you need to understand that a table is an unordered list. Obviously the record must be held in some physical sequence, but when you select records from a table into a recordset (using SQL SELECT statement) the order of the rows returnned is indeterminate, unless you specifically give a ORDER BY clause in the SQL, this means that if you leave out the ORDER BY clause, the rows may by chance be returned in the Order you want, but it is not guaranteed, hence your experience of sometimes it worked, sometimes it did not.

Regards

Ken Reay
Freelance Developer
kenneth.reay@talk21.com