Sorting an Access table using VBA

I'm looking for a simple line or two of code to sort an Access able by a specific field.  Thus far I have tried the following and some variations of it but without success.  I want to sort the field WtdAvgPctGoalMet in descending order.

Dim strSQL As String
    strSQL = "ALTER TABLE [table name] SORT COLUMN WtdAvgPctGoalMet Desc"
    CurrentDb.Execute strSQL

The above produces a syntax error message but gives no clue as to what should be changed.  Any suggestions would be appreciated.
Liberty4allAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
What is your purpose of sorting the data in the actual table when the records can be viewed in a sorted order?  On a form, you could either update the RecordSource property (RowSource property if a listbox) to include the Order By keyword (ie. SELECT [table name] ORDER BY WtdAvgPctGoalMet Desc), or you could just use the OrderBy property on the form itself.
0
Rey Obrero (Capricorn1)Commented:
try this codes, you have to create a query to do this

Dim strSQL As String, qd As DAO.QueryDef
If DCount("*", "msysobjects", "name='q1'") = 0 Then
   'create the query definition
    strSQL = "select * from TABLENAME order by  WtdAvgPctGoalMet Desc"
    Set qd = CurrentDb.CreateQueryDef("q1", strSQL)
End If
'open the query
   DoCmd.OpenQuery "q1"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Liberty4allAuthor Commented:
My reason for sorting records in the table is to do this prior to using code to add and populate a field that ranks them using the following code.

strSQL = "ALTER TABLE [table name] ADD COLUMN Rank COUNTER"
    CurrentDb.Execute strSQL

The table is populated from two different sources.  This means the values in it are not sorted in descending order which is necessary before running the code to add and populate the Rank field.  I was hoping to use code to perform the sort function first but can obviously do it by adding another query to the mix.  

I will accept the solution offerred by capricorn1 for future use.
0
Liberty4allAuthor Commented:
See my comments following those of capricorn1.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.