VB3 Retrieve Multiple Result Sets from a Stored Procedure

Last reviewed: January 9, 1997
Article ID: Q126992
The information in this article applies to:

- Professional Edition of Microsoft Visual Basic for Windows, version 3.0 - Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer

SUMMARY

Microsoft Access version 2.0 introduced a new type of SQL-specific Query called a SQL pass-through query. This allows you to execute SQL commands that are specific to a back-end database server such as Microsoft SQL Server. It also lets you group or batch multiple SQL statements that return multiple result sets or execute a stored procedure on the server that returns multiple result sets.

The information in this article shows you how to take advantage of this new query type to execute a batch of SQL statements that select multiple result sets into temporary tables, which you can then open and modify within your Visual Basic program.

MORE INFORMATION

An SQL pass-through (SPT) query is one that is not parsed by the Microsoft Jet database engine but is instead passed directly to an ODBC back end for processing. SPT queries can be used to execute server-specific functionality such as stored procedures.

There are two types of SQL pass-through queries: row-returning and non-row- returning. Non-row-returning pass-through queries are those that do not return a result set such as a data-definition query. Row-returning pass- through queries are those that return a result set and can be used like any other query.

If a pass-through query can return multiple result sets, as can some stored procedure executions, then a Make-Table query can be placed on top of the SPT query to retrieve the multiple result sets. For example, suppose you have an SPT query called [Get_Multiple_Results] that returns three separate result sets. If you create a Make-Table query on top of the [Get_Multiple_Results] query, three tables will be created as a result of executing that query.

Here are two properties associated with an SQL pass-through query:

  • ODBCConnectStr, which is a string that contains the full ODBC connect string for connecting to the ODBC
  • ReturnsRecords, which you set to True if the query returns a result set and False if it does not.

Step-by-Step Example

This example shows you how to create temporary tables in a local Microsoft Access database by selecting multiple results from the Pubs sample database on Microsoft SQL Server.

  1. Create the following stored procedure in the Pubs sample database on Microsoft SQL Server:

    CREATE PROC mod_authors AS SELECT dbo.authors.* FROM dbo.authors WHERE dbo.authors.state='CA' UPDATE dbo.authors SET dbo.authors.state='CA'

          where dbo.authors.state='UT'
    
    SELECT dbo.authors.* FROM dbo.authors WHERE dbo.authors.state='CA'

    This code selects data from the authors table, changes the state for authors who live in Utah to California, and then selects the data again to verify that the records have been updated.

  2. Using Microsoft Access version 2.0, create a new database, and name it MULTRES.MDB. In this database, create an SQL pass-through query with the following SQL statement:

          mod_authors;
    

    Save this query giving it the name "BatchQuery"; remember to set the ODBCConnectStr and the ReturnRecords properties appropriately.

    NOTE: For more information on how to create an SQL pass-through query, please refer to your Microsoft Access User's Guide, pages 292-294.

  3. In Visual Basic, start a new project (ALT, F, N). Form1 is created by default.

  4. Add a Grid control (Grid1) and a command button (Command1) to the form.

  5. Add the following code to the Click event of Command1:

       Sub Command1_Click ()
          Dim db As Database
          Dim tb As Table
          Dim tmpv As Variant
          Dim i As Integer, j As Integer
    
          On Error GoTo ErrorHandler
    
          ' Open the Microsoft Access version 2.0 .MDB database that contains
          ' the SQL pass-through query. Be sure to enter the proper directory.
          Set db = OpenDatabase("C:\ACCESS\MULTRES.MDB")
    
          ' Loop through tables and delete any temp tables that
          ' will be created by executing the Pass-Through query:
          i = 0
          Do While i < db.TableDefs.Count
             If InStr(UCase$(Trim(db.TableDefs(i).Name)), "TMPTABLE") Then
                db.TableDefs.Delete db.TableDefs(i).Name
                i = 0
             Else
                i = i + 1
             End If
          Loop
    
          ' Execute the sql pass-through query BatchQuery. Because the query
          ' contains multiple SQL statements, the temporary tables are created
          ' here.
          db.Execute("Select BatchQuery.* into tmpTable from BatchQuery;")
    
          ' Refresh the table list:
          db.TableDefs.Refresh
    
          ' Initialize grid:
          Grid1.Rows = 2
          Grid1.Cols = 2
          Grid1.Row = 1
          Grid1.Col = 1
    
          ' Loop through all of the tabledefs in the .MDB and look for the
          ' ones created by the stored procedure:
          For i = 0 To db.TableDefs.Count - 1
             If InStr(UCase$(Trim(db.TableDefs(i).Name)), "TMPTABLE") Then
                Set tb = db.OpenTable(db.TableDefs(i).Name)
    
                ' See if this result set has more columns than present:
                If Grid1.Cols <= tb.Fields.Count Then
                   Grid1.Cols = tb.Fields.Count + 1
                End If
    
                ' Get the column headings:
                For j = 0 To tb.Fields.Count - 1
                   Grid1.Col = j + 1
                   Grid1.Text = tb.Fields(j).SourceField
                Next j
    
                ' Load the grid:
                Grid1.Rows = Grid1.Rows + 1
                Grid1.Row = Grid1.Rows - 1
                Do While Not tb.EOF
                   For j = 0 To tb.Fields.Count - 1
                      ' Check for NULLs:
                      tmpv = tb.Fields(j).Value: If IsNull(tmpv) Then tmpv = ""
                      Grid1.Col = j + 1
    
                      ' Check column width and adjust as necessary.
                      ' Make sure form and grid have the same font properties:
                   If Grid1.ColWidth(Grid1.Col) < Me.TextWidth(CStr(tmpv)) Then
                      Grid1.ColWidth(Grid1.Col) = Me.TextWidth(CStr(tmpv))
                   End If
    
                      ' Assign the value:
                      Grid1.Text = UCase$(Trim(CStr(tmpv)))
                   Next j
    
                   ' Move to the next record:
                   Grid1.Rows = Grid1.Rows + 1
                   Grid1.Row = Grid1.Rows - 1
                   tb.MoveNext
                Loop
    
                ' Close current table:
                tb.Close
                Grid1.Rows = Grid1.Rows + 1
                Grid1.Row = Grid1.Rows - 1
             End If
          Next i
          db.Close
          Exit Sub
    
    
ErrorHandler:
      If Err <> 0 Then
         MsgBox Error$
      End If
      Exit Sub

   End Sub

  • Press the F5 key to run the program. Click the command button to see your grid filled with data from the two temporary tables you created.

    NOTE: The above sample works fine for smaller result sets. If you are returning a large result set, you may want to create the temporary tables on the server to avoid the network traffic of downloading all the data from the server to a local Access database.


  • KBCategory: kbprg kbcode
    KBSubCategory: APrgDataODBC
    Additional reference words: 3.00 ODBC Stored Procedure Result Set vb3only


    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: January 9, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.