Results 1 to 4 of 4
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Basic questions on how to reference the SQL pass through query from Access 2007

    Access 2007 front end, SQL 2008, backend. Small legal office. Windows 2007 Professional work stations, Windows 2008 Server, Domain established, AD implemented, ODBC working fine. Good VBA and data architecture skills.

    This is my first experience with using SQL server as a back end. I understand building your query and then selecting the SQL Pass-Through query option and seeing the translation to SQL as it is needed. Some articles say to copy and paste the code to SQL Server as a query in SSMS and/or as Stored Procedure or TSQL to cut down on the data transfer. (not sure I have that exactly correct).

    I understand converting Access SQL to SQL Server TSQL and changes in _ and " and '. I understand DoCmd.RunSQL.

    Three questions:

    1) If I create a SQL Pass-Through, do I save it locally just like a standard Access 2007 query so it shows up in the query list of Access 2007, but when it executes it runs it all on the SQL Server returning only the results,like a pass-through?

    2) If I create a SQL Pass-Through, do I have to cut and paste it to a query window on the SQL Server? If I do need to place it on the SQL Server, how do I reference the query from the front end?

    I have read a number of articles which discuss the process of creating the query but they are vague or varried on where it must be saved and how it is referenced. Or perhaps I just didn't understand it.

    3) I read another response that said Access 2007 is far more discerning on the data it returns and this is not the solution to query execution that everyone says it is.

    Thanks Phred

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    1) Yes; it will have a little world icon next to it.

    2) No need. Sometimes I may get the SQL working right in SSMS then paste the SQL to Access, but the query does not need to exist on the server. You may be thinking about executing a stored procedure with a pass through. In that situation, the stored procedure would exist on the server and the pass through would just be "EXEC StoredProcedureName", perhaps with parameters.

    3) Access is pretty good at letting the server do the work when it can, so some queries wouldn't perform any faster as pass throughs than they do as Access queries against linked tables. I often start with Access queries and convert when I run into performance issues.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Thanks Paul:

    Appreciate the clarity. I understand.

    Phred

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, post back if you have more questions. My friend Leigh posted a nice tutorial here:

    Beginning Sql Server Development - UtterAccess Discussion Forums
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 08-29-2011, 03:36 AM
  2. Basic Questions
    By jlclark4 in forum Access
    Replies: 6
    Last Post: 12-13-2010, 12:09 PM
  3. Basic commands in Access 2007
    By johnkl49 in forum Access
    Replies: 2
    Last Post: 09-23-2010, 04:07 PM
  4. Access 2007 questions
    By arzoo in forum Access
    Replies: 1
    Last Post: 09-10-2009, 07:39 AM
  5. Access 2007 Attachments - Relative reference
    By soringc in forum Programming
    Replies: 0
    Last Post: 11-22-2007, 10:47 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