How select statement write on Access view with MSSQL data source?

Posted on 2008-11-17
Last Modified: 2012-05-05

I created views in MSSQL 2005 express to view tables of a DB. I use ODBC to connect the views in MS ACCESS. I can see the views of those tables set in MSSQL.

However, how should I write the select statement in ACCESS to sort the views in MSSQL?

Question by:techques
    LVL 44

    Expert Comment

    by:Leigh Purvis
    A simple select statement will be effectively ANSI standard in both T-SQL and Jet SQL.
    (i.e. it should look effectively the same).
    SELECT * FROM LinkedViewName
    ORDER BY FieldName
    Is that what you meant by "sort the views"?
    Or are you wanting an ordered list of the Views you have linked to?
    Perhaps if you describe the greater objective we can offer a more precise suggestion?

    Author Comment


    I want to make a select statement in ACCESS

    select * from table where name = 'abc' order by id desc

    As it is an ACCESS view link to DB view, I do not know how can I do it in ACCESS
    LVL 44

    Accepted Solution

    Oh - you mean how to just create a query in Access?
    You don't mention a version - in 2007 you'd need to view the Create tab on the ribbon (as I recall :-s) but in previous versions you can choose:
    Insert Menu > Query > Design View > View > SQL
    Then write your SQL and save the query as whatever name you choose.
    That query is then effectively a View (though Access/Jet queries are a sort of limited hybrid between Views and SPs - in that they can be ordered, accept parameters etc).

    Author Comment

    I use Access 2002
    LVL 44

    Expert Comment

    by:Leigh Purvis
    OK - then as directed above then?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now