When to use ADODB.Command Object in ADO Programming
There are actually several more ways to create a recordset besides these,
both the connection and command objects support the Execute method, plus
stored procedures in the connection's default database are mapped into the
connection object as methods.
Use a command object when you need to pass parameters to either a stored
procedure or a parameter query. An example of a parameter query would be
"SELECT [name] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?",
passing the name of a table as a parameter. Parameter queries will tend to
perform better, particularly if SQL Server is the db engine, and you're
using complex queries, because it's easier to cache the query plan for them.
Explicitly create a recordset object when you need to control the cursor
type, lock type, or you need to be able to disconnect the recordset.
If all you need is a read-only forward-scolling recordset, simply use the
connection object to execute a SQL statement, e.g.,
' assumes cn is an open connection object
Dim rs1, rs2
Set rs1 = cn.Execute("exec sp_lock")
Set rs2 = cn.Execute("SELECT * FROM MyTable")
Note however that once a connection has been used to create a read-only
forward-scolling (aka 'firehose') cursor, that connection can only be used
to create more firehoses, it must be destroyed and recreated before it will
open any other type of cursor.
-Mark
Quote:
> I want to know when to use ADODB.Command Object in ADO Programming? For
> example, the following 2 approaches are working fine, but I don't know
> what's the differences, and which approach should I use in ADO
programming?
> Also, I am confused which cursor type and lock type I should use,
different
> books use different types but don't have clear explainations.
> Approach #1: don't use ADODB.Command object
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> 'step 1: open a database connection
> cn.Open connectionString
> 'step 2: open a recordset
> rs.Open sqlstatement, cn, adOpenKeyset, adLockReadonly
> 'step 3: able to access recordset fields
> Print.Debug rs.Fields("field1")
> Approach #2: use ADODB.Command object
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim cmd As New ADODB.Command
> 'step 1: open a database connection
> cn.Open connectionString
> 'step 2: set the command with SQL
> Set cmd.ActiveConnection = conn
> cmd.CommandText = sqlStatement
> 'step 3: open a recordset
> rs.Open cmd, , adOpenKeyset, adLockReadonly
> 'step 4: able to access recordset fields
> Print.Debug rs.Fields("field1")
> Thanks!!
> John
