Recordsets/Command Objects/Best Practices

[belovedcej]

Ok - so there are numerous ways of handling execution of commands on the database. There are two that I use regularly. (FYI - I program almost exclusively in Access projects, so I don't know if there may be yet others when dealing with linked tables or an Access back end.)

My examples follow:

TYPE 1

Dim rst As New ADODB.Recordset
Dim cmd As New AdoDB.Command

With cmd
    .ActiveConnection = CurrentProject.BaseConnectionString
    .CommandType = adCmdStoredProc
    .CommandText = "my stored procedure name"
    .Parameters.Refresh
    .Parameters(1) = parameter1
    .Parameters(2) = parameter2
    Set rst = .Execute
End with

do something with my rst if applicable

Set rst = nothing
Set cmd = nothing

TYPE 2

Dim rst As new adodb.Recordset

Set rst = CurrentProject.Connection.Execute("name of stored Procedure (" & parameter1 & ", " & parameter2 & ")")

do something with my rst if applicable

Set rst = nothing

I know that otherwise involve explicitly referencing a connection object, too, but I've never tried that.

My question is this - are there situations in which one way is better than another? Are there reasons to explicitly declare a command object or is it preference?

So far, my highly scientific method is to use type 1 when I have lots of parameters and type 2 when I only have 1 or 2 parameters, for readability purposes.

Any thoughts? How do you guys handle this?