Article ID: 131534 - View products that this article applies to.
This article was previously published under Q131534
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article demonstrates two sample user-defined Visual Basic for Applications functions that you can use to pass parameters to an SQL pass-through query.
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.
NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/182568/EN-US/ )ACC97: Microsoft Access 97 Sample Queries Available in Download Center
An SQL pass-through query does not accept dynamic parameters as do other Microsoft Access queries based on attached ODBC tables. This is so because Microsoft Access does not process an SQL pass-through query; it sends the literal commands (written in the syntax required by the SQL server) directly to the server for processing.
To pass parameters to an SQL pass-through query, create a Visual Basic function that builds and runs a QueryDef object using an SQL statement that concatenates the parameter criteria as literal values. Examples 1 and 2 show you how to create the function.
Example 1This sample function creates a QueryDef object that uses the "sp_addgroup" system procedure from Microsoft SQL Server to add a new group. The new group name is passed to the function and then concatenated into the SQL statement for the QueryDef object. To create this sample function, follow these steps:
Example 2This sample Visual Basic function creates a QueryDef object that uses the "sp_server_info" system procedure from Microsoft SQL Server to display attributes about the server. The attribute's number is passed to the function and then concatenated into the SQL statement for the QueryDef object. To create this sample function, follow these steps:
For more information about the syntax for the SQL property of the SQL pass-through query, see the documentation for your ODBC database server.
For more information about returning values from SQL stored procedures, please see the following article in the Microsoft Knowledge Base:
128408For more information about pass-through queries, search for "pass-through query," and then "Send commands to an SQL database using a pass-through query" using the Microsoft Access 97 Help menu.
(https://support.microsoft.com/kb/128408/EN-US/ )ACC: How to Return Values from SQL Stored Procedures
Article ID: 131534 - Last Review: January 19, 2007 - Revision: 3.4
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.