Most efficient way to use stored procedures

[StrikeEagleII]

I've been tinkering around with an access database I have the task being to change the backend to sql server. I programmed a simple stored procedure that takes one parameter and runs a select query using that parameter in the where clause.

Back in my front end, I want the results of the stored procedure to be the rowsource of a combo box.

What's the best way to add a parameter for the sp? I've got two ways working:

'this method requires rowsource of combo box to be set to a saved pass through query which is then updated:

    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb
    Set qry = db.QueryDefs("pt_APByModel")
    strSQL = "Exec usp_APByModel '" & ModelID & "'"
    strSQL = Replace(strSQL, "''", "") 'to account for Nulls
    qry.SQL = strSQL
    qry.Close
    Set qry = Nothing: Set db = Nothing
    Me.cboEffectivity.Requery

This is using ADODB:

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim strCNN As String
    strCNN = "Provider=sqloledb;" & _
           "Data Source=JASON-DESK\SQLEXPRESS;" & _
           "Initial Catalog=TOLADV;" & _
           "Integrated Security=SSPI;"
    cnn.Open strCNN
    cnn.CursorLocation = adUseClient

    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "usp_APByModel"
        .NamedParameters = True
        .Parameters("@ModelID") = ModelID
        Set rs = .Execute
    End With
    Set Me.cboEffectivity.Recordset = rs

    cnn.Close

Both of these seem long and clumsy to set the row source of 1 control--is there not a better way or do would I need to do this for all my combo and list boxes on the form?

 

[dhookom]

I use the first method. However, I use a simple ChangeSQL() function that accepts arguments of the query name and the new SQL. Something like:

Public Function ChangeSQL(strQueryName As String, -
        strSQL as String) as String
    Dim db as DAO.Database
    Dim qd as DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQueryName)
    ChangeSQL = qd.SQL 'return old SQL for kicks
    qd.SQL = strSQL    'set new SQL
    Set qd = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

I would use the ADO model, but a little bit refined:
I would set the connection in the Load event of the form, then cycle through the controls to get all list and combo boxes. Each of them might contain the name of the corresponding stored procedure in their Tag property.

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim strCNN As String
    strCNN = "Provider=sqloledb;" & _
           "Data Source=JASON-DESK\SQLEXPRESS;" & _
           "Initial Catalog=TOLADV;" & _
           "Integrated Security=SSPI;"
    cnn.Open strCNN
    cnn.CursorLocation = adUseClient

    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
    
        For Each ctr in Me.Controls
           Select Case  ctr.ControlType
              Case acComboBox, acListBox
                  If ctr.Tag<>"" Then
                    .CommandText = ctr.Tag
                    .Parameters.Refresh
                    .Parameters(0) = ModelID
                    set ctr.Recordset=.Execute
                  End If
           End select
       Next
    End With

You can further refine this to be a public function that could work for all forms.

HTH


Daniel Vlas
Systems Consultant

 

[JoeAtWork]

I use the second method. Note that most of your ADO code could be put in a central procedure, especially the part opening an ADO connection, which would always be the same.

With a little work you could probably change your code so that setting the RecordSource of your control is one line of code.

 

[StrikeEagleII]

All good suggestions I will explore further. Thanks. Another question for exploring ADO--which is generally the best strategy to use? setting a connection when the user first enters the database and then using that connection for each form, or have each form use its own connection? typically only one form will be open at a time. If each form has it's own connection, whether to open the connection in the form load event (as suggested above) and then close it in the unload event, or open the connection, get the data, close the connection, and then when user wants to save the data, re-open the connection commit the data to the back end and re-close the connection. (I don't have any idea on how to do that, or if it's worth the extra programming I'm sure is required)