Sorting a Table

[Hound_Cat]

I know that you can view records in order by using the ORDER BY clause in a SQL statment, but how can I sort the records in the table via code so that the records will be in order when I bring it up in Access? Now it shows up at the end when I put the new entry into the database. BTW, I am using ADO to do this.

Thanks,
Todd

[Andrew_Fryer]

In Access you can set the filter property so that the records are sorted the next time you open it. To do this go into table view and from the Records menu select:

Records->Filter->Advanced Filter Sort

This will bring up a window very similar to the query designer. Simply select the field you wish to sort on and then select the sorted field to be ascending. Save this and the next time you open Access the table will be sorted.

Hope this helps

Andrew

[Iouri]

You can create an index on the field. It will sort this field

Iouri Boutchkine
[email protected]

[Hound_Cat]

The Access table is created by another program, so I can't modify the properties within Access. I'm using Satellite Forms which will copy the Palm tables to an Access file and vice versa. So, I need to be able to sort the table using ADO code in Visual Basic when I add items to the table.

Thanks,
Todd

[Iouri]

First, you can create the index through VB.
Second, using SatForms you can enter tables from palm to dbase (.dbf files) table and then using SQL enter it into Access using ORDER BY clause.

Iouri Boutchkine
[email protected]

[Hound_Cat]

Is there a way to do this in code? I would like to sort all the records in the Access database so that no matter what I view it in, it will be in order. Thanks.

[Iouri]

You cannot physically sort records in the table. The only way to have them sorted physically is to use SQL clause order by and insert records to the different table without index.

To view records in order you always can add index to the field that you want to sort. Records in the table will be sorted by this index

Iouri Boutchkine
[email protected]

[Hound_Cat]

Thanks all for your help.. I finally got it to work.

I grabbed a recordset using the ORDER BY clause, saved the recordset into an Array via GetRows, used an SQL statement to DELETE the recordset, and then repopulated the sorted fields from the Array into the recordset. Works great!

Todd