Home > How-To Library > Database

How to create and invoke a stored procedure in an Access database in Visual Basic 6.

****************************************************************
*       © 2007 CodeItBetter http://www.codeitbetter.com        *
*          This notice MUST stay intact for legal use          *
****************************************************************

'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 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.
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
End Sub

If you would like to submit your code here please us. Do not forget to mention your name. We are always thankful to each and everyone of you who submitted their code here.