Sorting Specific Fields in Table/Recordset in VBA

[jamesdeuce]

I have a table that I have appended new rows to via a loop and Recordset. So the Recordset contains the original data plus the new appended rows. What I am trying to do is SORT the new combined recordset (or table) that contains all of the new data in VBA. I have tried executing a Select Statement such as "Select * FROM Table ORDER By Field1, Field 2" but that doesn't work. I can't figure out the Recordset.Sort method. Any help or ideas are appreciated. I have scoured the internet but I can't find examples like what I am trying to do. Thank you for any assistance.

[June7]

Why doesn't the SELECT work, what happens - error message, wrong results, nothing?

Never used Recordset.Sort. http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

[jamesdeuce]

Because you can't CurrentDb.Execute(SelectQuery) in VBA on a SELECT Query. There has to be a way I can "open a table" and merely sort it by a few fields from vba. Whether that's putting into a Recordset or something idk. I don't necessarily want to put it into variables/Recordset but I will if that will get the job done. I'm not a great programmer...I am educatedly guessing.

[June7]

Use VBA to open a recordset with SELECT query. But then what would you want to do with the recordset?

The order of records in table is irrelevant. AFAIK, programmatically applying sort criteria on a query object means VBA modifying QueryDefs.

Open a form and apply criteria to Order By property. Or set static ORDER BY parameters in form or report RecordSource.

[jamesdeuce]

@June7. 1) I don't really understand what you are saying. 2) I want the table organized so that when I open it I can see the data better and so that there's structure when I follow the data along in future modules that I build. I appreciate you trying but unless you dumb it down for me or post some specific examples, what you have said thus far doesn't help me as I just don't understand. I guess at the end of the day it doesn't matter the sort order but I would like it sorted for my sanity. And I don't think what I am asking to do is that difficult for an experienced programmer. All I want to do is have VBA take my specific Access table, sort by Field A, then Field B...save the table and close it...so that when I am looking at the table in Access it's in order. It's the last and final line of my VBA code...the dirty work has already been done. Perhaps someone else has some new ideas for me.

[jamesdeuce]

This may sound dumb but how do you run the below statement from VBA? Does it just sit there by it's own? Do you need to pass it into a Recordset? Does VBA just automatically Execute it? All I want to do is to get the below statement to run in VBA and re-order my table.

SELECT [Table].*
FROM [Table]
ORDER BY [Table].Date, [Table].Location

[June7]

Can't run that from VBA as is. SELECT sql is used in VBA when opening a recordset object or to programmatically set a form/report RecordSource property or a combo/list box RowSource property.

As I said, record order in table is irrelevant. Use queries, forms, reports to manipulate records.

I have never tried to programmatically set the ORDER BY property of a table. Open a table in design view and look at the Property Sheet. You will see Order By property. This can be set and the table saved. Might be able to programmatically modify this property by using TableDefs collection but I have never tried. If I do open a table and want to see records in a particular order, I apply sort criteria with Access ribbon (or right click) tools but I never save the table with any enforced order or filtering.

[jamesdeuce]

One of my problems was the "new" tables that came in from VBA Unsorted did not have Indexes. When I was able to figure out how to Index them via VBA, they inherently sorted themselves. So in essence my problem is solved. But in the future I am sure I am going to run into a problem where your advice will definitely be of some help. Thank you for writing me back in a timely manner and providing input. I appreciate your help.