Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server Stored procedures (2000)

    I

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Server Stored procedures (2000)

    To answer your last question first, there really isn't an easy way of creating work tables in Access using a stored procedure. There are several ways of enhancing the performance when using an Access front-end to work with SQL Server tables. Let me list several, and then suggest some further resource for reading:<UL><LI>One of the simplest to implement is to be sure all tables are in the SQL back-end. Doing that ensures that the ODBC driver can optimize the query being sent to SQL Server, and most of the work gets done in the back-end.
    <LI>Creating views in SQL Server can also enhance performance in some special cases - users simply see it as a table, but there may be some issues with updatability if that is a consideration.
    <LI>Using ODBC-direct queries can also let you tune a particular query to get better performance in SQL Server, but you need to understand the intricacies of writing SQL Server queries and how they get optimized. Not something most users could easily do.
    <LI>Pass-through queries are sent directly to SQL Server, but cannot use parameters, which limits their usefulness for most users. You can however write code to manipulate the QueryDef, and accomplish the same thing, but you have to anticipate the kinds of questions they want to answer - definitely not for ad-hoc situations.

    Take a look at the details of how these things work in either the Access Developer's Handbook, or in Alison Balter's Volume 2 of Mastering Access 2002 Database Development (it mostly applies to 2000 as well, and is a nice pragmatic look at how to do things).
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Stored procedures (2000)

    Lots in that answer, thanks. A couple of follow up questions:
    1) Do you mean the Getz Letwin etc. Developers Handbook. I've got both books for Access 2000. Is the other book better?
    2) Can we use SQL views as if they were linked tables? All the Access applications are read only so not being able to update will not be a problem.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Server Stored procedures (2000)

    1. <LI>Yes - you can find details here. The other book is not necessarily better - it's aimed at a somewhat different intermediate to advance user - check it out on Amazon
      <LI>Yes - SQL views can be linked to just like a table can be - we typically give them a unique prefix (vw_ for example) so that we know a given linked table is actually a view. The only challenge has to do with being able to update it, but if you tell Access what the primary key is, then it will let you do updates. Another option is to create an indexed view in SQL, but that's a fairly advanced operation in SQL Server.
    Wendell

Posting Permissions

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