Code for sorting Access Tables in VBA 1

[russeldraper]

Hello,

If I have a table called 'Mort' and a column in that table called 'Age', I was wondering if there was a way I could sort the table Mort (in ascending order, ie 0..100) in VBA by the column Age. And what the code for that sort would be.

Thanks for the help

 

[grtammi]

Hi russel,

I don't know of anyway to sort the table per se, but you could use a little VBA and an SQL statement with an ORDER BY clause, such as follows:

dim strSQL as string
strSQL = "SELECT * FROM tblMort ORDER BY tblMort.Age ASC"

You could then programmatically assign the value contained in strSQL to a form or report (or, you could even re-create the table, but ... why?), by setting the value of the selected object's RecordSource property:

Forms!<YourFormName>.RecordSource = strSQL
Forms!<YourFormName>.Requery   ' gotta refresh the records

Anyways ... hope this helped you in someway.

Greg



Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[RickSpr]

One of the basic principles of relational databases is that data in tables has no inherent ordering. That makes it possible for the SQL processing logic to choose more efficient algorithms when joining tables and selecting rows.

Instead of sorting it in the table, you sort the data as you retrieve it, using the ORDER BY clause (or the equivalent Sort: row in a query grid.)

In a report, you can also sort it to do grouping.

If you want to sort it "in the table" so that a table datasheet shows it in a particular order, you can change the OrderBy and OrderByOn properties of the TableDef object. These are Access properties added on to the Jet object, so you have to use the TableDef.Properties collection to get to them. Also, the properties won't exist the first time you try to set them, so you'll get an error. You have to trap the error and use CreateProperty to create them. For example code, see the CreateProperty method in the Help file.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein