Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Views vs Access queries (Access 2003 sp2)

    Is there an advantage using views rather than Access queries?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    A view is like a stored subset of data. It resembles a SELECT query in many ways, and in situations where you can use a view, it is very efficient because a view is optimized the moment it is saved. But there are things a query can do but a view can't:
    - You can't use ORDER BY in a view (except in a TOP n view)
    - You can't use parameters in a view.
    - A view itself cannot update, insert or delete records (but you can create an update, append or delete query based on a view)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    Great explanation thank you Hans.
    When you say a view cannot have parameters can it refer to a global variable, im guessing the answer is no.

    Am i right in assuming that it would be fast because the view is executed on the server, as opposed to Access?

    Does Access in fact read all the data from the table and execute the query on the users pc?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    A parameter query is a query that prompts the user for a value, e.g. [Enter Country]. Views don't support parameters.

    A view is executed on the server. With an Access query based on SQL Server data, you can specify whether the selection will be executed in Access (the default) or on the server. To specify the latter, select Query | SQL specific | Pass-through query.
    In the default situation, *all* data will be read by Access to determine which records satisfy the criteria. For a pass-through query, the criteria are applied on the server, and only records satisfying the criteria will be passed to Access.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    Thanks Hans

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    So are pass-through queries as fast or as powerful as views?

    Can pass-through queries have parameters?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    I'm not an expert in this, but I think a view is theoretically more efficient, but whether you'd notice the difference? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    A pass-through query doesn't accept parameters. But you can simulate it by changing the SQL statement of a pass-through query in code to include the literal value of parameters. See How to Simulate Parameters in an SQL Pass-Through Query (it's for Access 97 but it should work the same in later versions)

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    I thought as much re changing the SQL on the fly.

    Does anybody else know?

    Thanks

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    I do contract work for a company that has 2 Access applications that use SQL backend, and are in wide distribution to alot of clients. Initially, they started using Views, but we have gradually switched over to using pass-through queries. If there is a performance difference, I'd have to say it is not worth mentioning.

    You can't use parameters in pass-throughs. You need to completely form the SQL string in code, then modify the .SQL property of the pass-through query with the new string.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    Thanks Mark, i will keep that in mind.

    I have used the SQL property to update queries in the past.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Views vs Access queries (Access 2003 sp2)

    Sorry Hans i assumed your answer was something else.

    I will keep that in mind too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •