Results 1 to 6 of 6
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    View Vs. Query

    (cant believe someone hasn't asked this but I couldn't find anything on here)

    Trying to understand what a "View" is and how it differs from a Query, I have tried google and the answer I got was beyond my ability to understand.

    I get that Views are stored in SQL Server, and Queries are kept local in Access but beyond that I couldn't understand the value of one over the other.

    hoping someone can give me an answer that is easier to understand than the one below.

    Thanks




    Click image for larger version. 

Name:	Capture.PNG 
Views:	46 
Size:	29.5 KB 
ID:	45618

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    A SQL view is a query but run on the SQL server.
    If you have a large number of joins, or some complex processing you need to see in a query output, doing it in a view can have significant performance advantages as all the heavy lifting can be done on the server.

    Another bonus is that a View can be editable in certain circumstances. This again can then be presented as a forms recordsource and make some types of updates a lot easier.
    SQL Views can also join on differing data types, and more complex joins than an Access query. They can also make use of Common Table Expressions (CTE's) which can be very useful for recursive operations and data presentations.

    The fact that you link to them as a table can also have benefits, as you can use them on the left side of a join in an Access query and the results are still editable, very handy in complicated continuous forms that need editing.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Although a view is a SQL Server query, it is treated as a linked table in Access.
    In order to make a view editable in Access, you must assign a primary key field to the view, either during linking using the ODBC wizard or subsequently using code.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Views are stored select queries that can be re-used to show the current data results. They behave as tables and can have their own security settings. Views can be based on a set of tables and/or views.
    Provided the base is an updateable query and the user has enough rights, they can be used to modify the underlying data.

    Advantages of using views:

    • You can combine data from different tables in one view
    • You can hide existing columns or add calculated columns
    • Views have their own security settings that can differ from the tables


    Restrictions:

    • Because a view behaves as a table, they have no logical order. This means you cannot add an ORDER BY statement, except with a TOP n operator. In this case the top n records will be returned, but the recordset itself will still be without any order.
    • Views can only be created from permanent tables, not from temporary tables.
    • You cannot pass parameters to views.


    Views are often added to present the users with an extra data layer where they can only see the columns they are entitled to view and where the joins between tables have already be premade by the data administrator or developer.

  5. #5
    Join Date
    Oct 2021
    Posts
    9
    I could write a book about this. Lol

    To keep it simple, view is a saved query or compiled query. You create or alter a query to save it as a view.

    Query is something you type to get results.

    Ex:

    select count(1), productname
    from table
    group by productname

    If you don’t want to type the query every time, save it as a view.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Be careful, to many views can lead to complicated and inefficient query plans and slow down your SQL server.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-29-2018, 03:18 PM
  2. Replies: 10
    Last Post: 02-06-2018, 02:37 PM
  3. Replies: 2
    Last Post: 03-05-2015, 01:10 PM
  4. Replies: 8
    Last Post: 04-29-2013, 11:23 AM
  5. Unable to view query in design view
    By vemi007 in forum Queries
    Replies: 7
    Last Post: 01-19-2012, 11:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums