Access Cookbook

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

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.

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