G
George King
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
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