By reusing the same ADO command, you save time needed to create the ADO object, making execution faster. To reuse, commands you must use a transaction as an envelope around the commands.

You can reuse commands for INSERT and SELECT statements whenever SQL is repeated, with different parameter values for each iteration of the SQL.
Reusing commands for UPDATE and DELETE statements works only with non-relational data sources.

The following code shows how to reuse code to insert rows into a table. Two methods are shown: the first method inserts rows via parameters and the second method uses constants. The second method is translated internally to be the same as the first method and is thus a little slower. Using either method is a matter of preference.

Using constants works only for INSERT statements and not for SELECT statements.

Dim conn As ADODB.Connection
Dim com As ADODB.Command
Dim dept_id(10) As String
Dim dept_budget(10) As Double

’Init values for the new rows
dept_id(1) = "DP11"
dept_id(2) = "DP12"
dept_id(3) = "DP13"
dept_id(4) = "DP14"
dept_id(5) = "DP15"
dept_id(6) = "DP16"

dept_budget(1) = 11
dept_budget(2) = 22
dept_budget(3) = 33
dept_budget(4) = 44
dept_budget(5) = 55
dept_budget(6) = 66

On Error GoTo Error_Handler
Set conn = New ADODB.Connection
conn.Provider = "AttunityConnect"
conn.Open

’NOTE: 1. It is important to use a transaction in order
’ to enable the reuse.

’2. All the ADO commands to reuse
’ must be created within this transaction.

conn.BeginTrans

’Two methods for efficient insertion of rows. Each
’ method inserts 3 rows in a loop to the dept table

’ 1. Same command, reusing executed state, using parameters.
’================================================= ==========

Set com = New ADODB.Command
Set com.ActiveConnection = conn
com.CommandType = adCmdText

’We set the command text once and reuse it.

com.CommandText = "insert into dept values (?,?)"
com.Parameters.Append com.CreateParameter("p0", adChar, adParamInput, 4)

com.Parameters.Append com.CreateParameter("p1", adDouble, adParamInput, 0)

’Insert 3 new rows

For i = 1 To 3
com.Parameters(0) = dept_id(i)
com.Parameters(1) = dept_budget(i)
com.Execute

’There is no need to create a new command or even to set
’ text again in every iteration.

Next i

’cleanup
Set com = Nothing

’2. Same command, reusing executed state, using constants.
’================================================= ========

Set com = New ADODB.Command
Set com.ActiveConnection = conn
com.CommandType = adCmdText

’We don’t create a new command for every iteration,
’ just set the new command text

’Insert additional 3 new rows
For i = 4 To 6
com.CommandText = "insert into dept values (’" & dept_id(i) & "’," &dept_budget(i) & ")"
com.Execute
Next i

’cleanup
Set com = Nothing

’Finish
conn.CommitTrans
conn.Close
Exit Sub

Error_Handler:
If conn.Errors.count > 0 Then
MsgBox conn.Errors.Item(0).Source & " : " &
conn.Errors.Item(0).Description
End If
conn.RollbackTrans
conn.Close
End Sub