Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Thanked 0 Times in 0 Posts

    Access 2002...sql server (2002)

    Hi guys, firstly i would like to thank all those have contributed to helping me out with all these questions. There is one thing that i would really like to know though before i get deeper into changing things, when would you say it is best to use a stored proceedure, or a view instead. Obviously there must be limitations on both of these as to what situation to use them in, i just wondered what sort of questions i should be asking myself before making a decision on what way to go.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts

    Re: Access 2002...sql server (2002)

    I would suggest you look at the Books Online documentation for detailed information, but broadly speaking a view is a virtual table which can be used in the same ways a real table can, even though it is actually stored as T-SQL instructions rather than as a table. You can, for example, link a view as a table to an mdb via ODBC. There are certain limitations with views, such as that you cannot use temporary tables, but they are essentially a wrapper around a select statement. Within a view you can retrieve data from multiple tables using the relevant joins and/or criteria and users can then query this data as though it were one table rather than having to create complex queries.
    Typically, the data in your database will have been normalised - e.g. you may have a customers table, a suppliers table, an orders table and an order details table, with relationships defined between the tables. As a result, in order to get the data in a form that users will understand (so they see supplier names rather than IDs etc.), you will need to link the various tables together and you would need to do this in every query you write for that data. If you create a view that provides all the data in its required format, you can then write queries (or stored procedures) off this view just as if it were all stored in one big table.
    Hope that helps.

    Microsoft MVP - Excel

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Access 2002...sql server (2002)

    In general I would recommend views where they are feasible. We use them frequently when we attach via ODBC and an MDB front-end - they look just like tables, but we get the performance of SQL Server in returning the exact records we want. You can also index a view, which makes it updatable when needed. If you haven't already seen it, I would suggest you acquire a copy of Microsoft Access Developer's Guide to SQL Server by Chipman and Baron. It will give you lots of advice and lots of techniques. Also note that it can be downloaded in digital format.

Posting Permissions

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