Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 959
  • Last Modified:

Access:Make my VB Sort SQL code work

I would like my VB code to sort all data in my table in the following way: Sort column1 then column then column3. The VB code is below.

DoCmd.RunSQL "SELECT Table1.* FROM table1 ORDER BY table1.column1, table1.column2, table1.column3

I keep getting an error message A runsql action requires an argument consisting of an SQL statement.

Will someone help me make this code work?

Ideally here is what my table should look like
2 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
DoCmd.RunSQL executes a query, which is handy for an action query like UPDATE, DELETE, INSERT, or MAKE-TABLE, but somewhat useless for SELECT as it does not return a recordset.

SELECT * FROM table1 ORDER BY column1, column2, column3

If you wish ot order the data within a table, I recommend against this, as the sole purpose of a table is to hold data, per specified format/schema, and not to sort data or display it in any cosmetic fasion.  
To do that, use queries.
Rey Obrero (Capricorn1)Commented:
you can save your query and just open it in VBA with

docmd.openquery "NameOFSavedQuery"

another way if you want to change columns of sorting is to to revised the sql of the saved query using querydef
ouestqueAuthor Commented:
Thanks for the tips above! But is there a way to use VB to permanently sort (as described above) table columns without using the query method proposed?

I have a MAIN table (Which is my user data), then VB code deletes table1, then pulls select data from the MAIN table and inputs it into table1. (Using docmd.runsql append&) Recordsets then manipulate that data. (For reports)

The only problem is that I need table1 to sort the columns so that the recordsets put everything in the right place.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now