View Single Post
  #2 (permalink)  
Old October 20th, 2003, 02:25 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Jimmy,

Here's an approach I picked up from the "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron.

This pass-through query executes a parameterized stored procedure against the SQL Server version of the Northwind database using a DSN-less ODBC connection.

The query returns a single employee record using EmployeeID as criteria.

First the pass-through query get's built, then a stored procedure gets created on SQL Server which the pass-through query will execute, then some code gets written to re-build the pass-through query everytime a new employee record is requested.

The ExecutePassThrough sub recieves an Employee ID number as an argument and concatenates it to the Pass-Through query's SQL. BuildPassThrough is then called which uses ADOX to reset the pass- through query's command text.

 ================================================== =======
 Design the Pass-Through Query in Access (.mdb)
 ================================================== =======

 1. In Query design view, from the main menu select
    Query | SQL Specific | Pass-Through.

 2. Type "EXEC up_parmsel_Employees" as the query's SQL
    statement.

 3. Click the properties button on the Toolbar to open the
    Query properties dialog. Enter the following DSN-less
    connection string in the ODBC Connect Str property:

    ODBC;Driver={SQL Server};Server=(local); _
    Database=Northwind;Trusted_Connection=Yes

    NT authentication is used here.

    All the other default properties are fine. Clicking
    the ellipsis in the ODBC Connect Str property
    field will open the DSN Data Source dialog if you
    want to use a DSN.

 4. Save the query as "qsptEmployees" and close without
    running (the stored procedure doesn't exit yet).


 ================================================== =======
 Create the Stored Procedure in SQL Server 2000
 ================================================== =======

 1. With SQL Server running open the Query Analyzer.

 2. Select the Northwind database form the Toolbar's
    drop-down list.

 3. Type the following SQL in the Query Analyzer, parse,
    and run:

    CREATE PROC up_parmsel_Employees
      @EmployeeID int = NULL
    AS
    SELECT EmployeeID, LastName, FirstName, Title
    FROM Northwind.dbo.Employees
    WHERE EmployeeID = @EmployeeID

 4. Test that the proc was created successfully by
    clearing the Query Analyzer window and running the
    following command in the Query Analyzer:

    Exec up_parmsel_Employees 1

    A single employee record for "Nancy Davolio" should be
    displayed. The query and the proc it executes are now
    ready to go. Just need code to set the parameter that
    the proc expects.


 ================================================== =======
 Write the Parameter Setting Module in Access
 ================================================== =======

 1. Open a new module in Access and set a Reference to
    ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
    library.

 2. Paste in the following two subs:

     Public Sub ExecutePassThrough(lngParam As Long)
       Dim strTSQL As String
       Dim strQueryName As String

       strQueryName = "qsptEmployees"
       strTSQL = "EXEC up_parmsel_Employees " & lngParam

       Call BuildPassThrough(strQueryName, strTSQL)

       DoCmd.OpenQuery strQueryName
     End Sub


     Public Sub BuildPassThrough( _
       ByVal strQName As String, _
       ByVal strSQL As String)

       Dim cat As ADOX.Catalog
       Dim cmd As ADODB.Command

       Set cat = New ADOX.Catalog
       Set cat.ActiveConnection = CurrentProject.Connection

       Set cmd = cat.Procedures(strQName).Command

       ' Verify query is a pass-through query
       cmd.Properties( _
          "Jet OLEDB:ODBC Pass-Through Statement") = True

       cmd.CommandText = strSQL

       Set cat.Procedures(strQName).Command = cmd

       Set cmd = Nothing
       Set cat = Nothing

     End Sub


 ================================================== =======
 Test the Procedures, Resetting the Queries Parameters
 ================================================== =======

 1. In the immediate window, type the following and hit
    Enter:

    ExecutePassThrough (1)


 2. A query should open in datasheet view displaying Nancy
    Davolio's employee record.

    Or you could return the result set to an ADO recordset
    with:

    Set rst = New ADODB.Recordset
    rst.Open strQueryName, CurrentProject.AccessConnection

    You could use the following syntax:

    lngParam = [Forms]![frmSearch]![txtSearchValue]
    Call ExecutePassThrough(lngParam)

    to get the parameter value from a form.

HTH,

Bob

Reply With Quote