Can someone confirm this behaviour or point out the error I'm making?

I've adapted a procedure from Professional Excel Development (errors will be
mine) to try to update from Excel a table in Access. It executes a stored
update query in the Access database. It sets up the values for six
parameters from cells in the worksheet (I can't generate an SQL statement
because one of the columns includes multiple ""' type characters i.e.
termination characters). It works fine if it's executed once but in a loop
to go down the rows it still only executes once. I've checked that the
parameters are being updated by printing them to the immediate window and I
also modified the command.execute to only run if i = 3 (say) and it executed
with the appropriate parameter values. Oh and the lAffected (the return
value from the execute command that shows the record affected by the
operation) is always 1. I can't find anything to explain this behaviour.

Many thanks

George

Public Sub UpdateAccess()

Dim cmAccess As ADODB.Command
Dim objParams As ADODB.Parameters
Dim lAffected As Long
Dim sDataSourceFilePath As String
Dim sConnect As String
Dim i As Integer, j As Integer
Dim rngData As Range
Dim lAffectedTotal As Long
Dim datStart As Date
Dim datEnd As Date

datStart = Now()
sDataSourceFilePath = "\\LEH\FID\GROUPS\FI_STG\FI_STG\Fixed Assets\db1.20021.
mdb"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSourceFilePath & ";"

' Create the Command object.
Set cmAccess = New ADODB.Command
cmAccess.ActiveConnection = sConnect
cmAccess.CommandText = "qupP_M_template"
cmAccess.CommandType = adCmdStoredProc

' Create and append the parameters.
Set objParams = cmAccess.Parameters

With objParams
.Append cmAccess.CreateParameter("xlpmvalue", adNumeric,
adParamInput, 1)
.Append cmAccess.CreateParameter("xlentityName", adVarChar,
adParamInput, 255)
.Append cmAccess.CreateParameter("xlCategory", adVarChar,
adParamInput, 255)
.Append cmAccess.CreateParameter("xlProfile", adVarChar,
adParamInput, 255)
.Append cmAccess.CreateParameter("xlAssetClass", adVarChar,
adParamInput, 255)
.Append cmAccess.CreateParameter("xlDescr", adVarChar, adParamInput,
255)

End With

Set objParams = Nothing

' Load the parameters and execute the query.

Set rngData = shtData.Range("testdata")

For i = 1 To rngData.Rows.Count

For j = 1 To 6

'parameters collection is zero based
cmAccess.Parameters(j - 1).Value = rngData.Cells(i, Choose(j, 8, 1,
2, 3, 4, 5))

Next j



cmAccess.Execute lAffected, , adExecuteNoRecords
lAffectedTotal = lAffectedTotal + lAffected
'MsgBox "This loop " & lAffected & " records processed" & vbCrLf &
vbCrLf & _
"Total so far " & lAffectedTotal
'
Next i


' Display number of records updated.
'MsgBox lAffectedTotal & " records updated"

Set cmAccess = Nothing

datEnd = Now()
Debug.Print Round((datEnd - datStart) * 24 * 60 * 60, 0) & " secs for data
update of " & lAffectedTotal & " records"


End Sub