How To Call a Stored Procedure Using the Data Control

MORE INFORMATION

In order to pass SQL Server syntax through the JET engine (the engine that allows Visual Basic to connect to an Access databases), the dbSQLPassThrough option must be used. This prevents the parser built into JET from attempting to parse the SQL string.

This ability to pass through the JET engine allows the Visual Basic programmer to call stored procedures on the SQL Server database. When using the Data Control, you can set the SQLPassThrough option to use the Options property of the data control.

The Options property Online Documentation describes dbSQLPassThrough as follows:

Constant          Value  Description
---------------------------------------------------------------------------
dbSQLPassThrough  64     When using Data controls with an SQL statement
                         in the RecordSource property, sends the SQL
                         statement to an ODBC database, such as a SQL
                         Server or Oracle database, for processing.
				

The complete list of valid settings for the Options property is documented in the Online Help. Search there for Options Property.

Step-by-Step Example

The following sample uses the pubs database in SQL Server to demonstrate the pass-through behavior. This contains a stored procedure called "byroyalty" that takes an integer and returns all authors that match the percentage royalty:

1. Start a new program in Visual Basic. Form1 is created by default.
2. Add a Data Control and a Text Box to Form1.
3. Set the DataSource property of Text1 to Data1.
4. Add the following code to Form1.
   
   Note You must change UID =<username> and PWD =<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

         Private Sub Form_Load()
            Data1.Connect = "ODBC;DSN=DSNToPubs;" & _
                            ";Database=pubs;" & _
                            "Uid=<username>;Pwd=<strong password>;"
            Data1.RecordSource = "Exec byroyalty 40"
            Data1.Caption = "Stored Procedure Test"
            Data1.Options = Data1.Options Or dbSQLPassThrough
            Data1.Refresh
   
            Text1.DataField = "Au_ID"
         End Sub
   
   						

5. Press the F5 key to run the program. You should see the first author ID in the Text Box.