Access:Make my VB Sort SQL code work

Posted on 2007-10-15
Last Modified: 2013-11-27
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
Question by:ouestque
    LVL 65

    Accepted Solution

    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.
    LVL 119

    Assisted Solution

    by:Rey Obrero
    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

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now