Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
افزودن یادداشت
افزودن یادداشت جدید










Recipe 14.5 Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB



14.5.1 Problem


You are calling stored procedures
that require parameters by using pass-through queries. How can you
pass parameters to the pass-through query from your form? If you
include a reference to the form in the pass-through query, you get an
error message from SQL Server.


14.5.2 Solution


Pass-through queries are not processed in the same way as regular
Access queries against linked tables. The SQL syntax you type in a
pass-through query is passed directly to SQL Server. Any references
to forms or controls on forms in a pass-through query are meaningless
to SQL Server, so you must pass the actual values for your
parameters.

A pass-through query has three important
properties:

SQL


The
SQL property contains the textual content of the pass-through query.
This must be a valid Transact-SQL statement.


ODBCConnectStr


The connection string contains
information that the query uses to connect to SQL Server. You can
specify a DSN, or use a string containing all the requisite
connection information, as shown in the Solution in Recipe 14.1.


ReturnsRecords


The ReturnsRecords property specifies
whether or not the query returns records. An action query that just
modifies data without retrieving anything would have this property
set to No or False.



Figure 14-12 shows the properties sheet for a
pass-through query to the pubs sample database in
SQL Server.


Figure 14-12. Pass-through query properties


The most
versatile way to set these properties is to write a procedure that
sets them at runtime by using a DAO QueryDef object.
You'll then need to set parameter values to the
procedure for connection information, the SQL string that comprises
the pass-through query, and whether or not the query returns records.

To
modify a pass-through query at runtime, follow these general steps:

  1. Open a new module and set a reference to the DAO object library.

  2. Create a new public procedure. Here is the complete code listing:

    Public Sub acbPassThrough( _
    ByVal QueryName As String, _
    ByVal SQLStatement As String, _
    Optional ConnectStr As Variant, _
    Optional ReturnsRecords As Boolean = True)
    Dim qdf As DAO.QueryDef
    Dim strConnect As String
    Set qdf = CurrentDb.QueryDefs(QueryName)
    ' If no connection information is supplied,
    ' connection information from the query is used.
    If IsMissing(ConnectStr) Then
    strConnect = qdf.Connect
    Else
    strConnect = CStr(ConnectStr)
    End If
    ' Set query properties to parameter values.
    qdf.Connect = strConnect
    qdf.ReturnsRecords = ReturnsRecords
    qdf.SQL = SQLStatement
    ExitHere:
    Set qdf = Nothing
    Exit Sub
    HandleErr:
    MsgBox Err & ": " & Err.Description, , "Error in acbPassThrough"
    Resume ExitHere
    End Sub
  3. To test the procedure, create a new query and choose Query
    SQL-Specific Pass-through from the menu.

  4. Save the query, naming it qryPassThrough.

  5. Create a form with text boxes and optionally a combo box to test the
    procedure. The sample form in

    14-05.MDB uses the
    byroyalty stored procedure from the
    pubs sample database. It takes an input parameter
    for the royalty percentage. You can change the values on the form
    shown in Figure 14-13 to adjust any of the arguments
    needed to call the acbPassThrough procedure.



Figure 14-13. The sample form used to test the acbPassThrough procedure


  1. Write the following code in the
    Click event of the command button to pass the parameters to
    acbPassThrough:

    Private Sub cmdExecute_Click( )
    Dim strQuery As String
    Dim strSQL As String
    Dim strConnect As String
    Dim fReturnsRecs As Boolean
    strQuery = Me.lblQuery.Caption
    strConnect = Me.lblConnection.Caption
    fReturnsRecs = CBool(Me.ckReturnsRecords)
    strSQL = "EXEC byroyalty " & Me.cboParameter
    Call acbPassThrough(strQuery, strSQL, strConnect, fReturnsRecs)
    Me.RecordSource = strQuery
    Me.txtAuID.Visible = True
    End Sub
  2. Test the procedure by clicking the "Execute
    byroyalty" command button on the form.



14.5.3 Discussion


The acbPassThrough procedure can modify any saved pass-through query
by using the DAO QueryDef object:

Dim qdf As DAO.QueryDef
Dim strConnect As String
Set qdf = CurrentDb.QueryDefs(QueryName)

There is an optional parameter for the ConnectStr argument. If a
connection string is not supplied, the one saved with the QueryDef
object is used:

If IsMissing(ConnectStr) Then
strConnect = qdf.Connect
Else
strConnect = CStr(ConnectStr)
End If

The properties for the query are then set to the values passed into
the procedure:

qdf.Connect = strConnect
qdf.ReturnsRecords = ReturnsRecords
qdf.SQL = SQLStatement

This actually permanently saves changes to the queryif you
open the query in design view after executing the procedure,
you'll see the last properties that were set.

The values on the form are simply collected from the relevant text
boxes and combo boxes, and passed to the procedure. Then the form is
requeried and the new results of the pass-through query are loaded as
the record source of the form.

Access lets you create ad hoc queries by
using the CreateQueryDef syntax and specifying an
empty string for the parameter name. However, using a previously
saved query eliminates the overhead of creating a new object from
scratch and then discarding it.


The result set returned from a pass-through query is always read-only.


/ 232