What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleCreate and invoke a stored procedure in an Access database
DescriptionThis example shows how to create and invoke a stored procedure in an Access database in Visual Basic 6.
Keywordsstored procedure, Access, database,
A stored procedure is a function stored inside a database. They are useful for several reasons. They let you change a function without recompiling the program and let you store database functionality with the data it manipulates. If the database sits on a network, the stored procedure can improve performance by examining many records and only returning a small result.

This program creates a stored procedure by executing the CREATE PROCEDURE statement.

' Create the stored procedure.
Private Sub cmdCreate_Click()
    ' Drop the procedure if it already exists.
    On Error Resume Next
    m_DBConnection.Execute "DROP PROCEDURE BookInfo"
    On Error GoTo 0

    ' Create the stored procedure.
    m_DBConnection.Execute lblProcedure.Caption

    cmdCreate.Enabled = False
    cboTitle.Enabled = True
End Sub
When the user selects a book title from a ComboBox, the program executes the stored procedure. It creates a Command object, adds a parameter to give the stored procedure the data it needs, and executes the command.
' Display information for this book.
Private Sub cboTitle_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim result As String
Dim txt As String
Dim i As Integer

    ' Create a command object.
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = m_DBConnection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "BookInfo"
    txt = cboTitle.Text
    cmd.Parameters.Append cmd.CreateParameter("title", _
        adVarChar, _
        adParamInput, Len(txt), txt)

    ' Execute the command.
    Set rs = cmd.Execute

    ' Display the results.
    txt = ""
    For i = 0 To rs.Fields.Count - 1
        txt = txt & rs.Fields(i).Name & " = " & _
            rs.Fields(i).Value & vbCrLf
    Next i
    lblResults.Caption = txt

    ' Close the recordset and free it and the command
    ' object.
    Set rs = Nothing
    Set cmd = Nothing
End Sub
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.