11.1 Remove a Form s System Menu and MaximizeMinimize Buttons

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 Section 14.1.2.

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

   

6. 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

7. 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 query if 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.