How do I get the number of records altered by a SQL command returned to my program using ADO. The code I am using is below and works great, but I want to be able to pass on to the user the number of records the SQL command actually affected.
Thanks!
-------------------------
Sub PurgeFundAmountRecs()
'This sub will remove all FundAmount records that are older than 4 years from
'from the FundAmounts table.
On Error GoTo PurgeFundAmts_Error
Dim SqlStr As String
Dim DeleteYear As String
Dim conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim adoerr As ADODB.Error
DeleteYear = Year(Date) - 5
Set conn = CurrentProject.Connection
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = conn
SqlStr = "DELETE FROM [FundAmounts] WHERE FundAmounts].YearID <= " & DeleteYear
Cmd.CommandText = SqlStr
Cmd.CommandType = adCmdText
Cmd.Execute RecordsAffected:=recs, Options:=adExecuteNoRecords
Set Cmd = Nothing
MsgBox ("FundAmounts table has been successfully purged"
Exit Sub
PurgeFundAmts_Error:
With conn
If .Errors.Count > 0 Then
For Each adoerr In conn.Errors
MsgBox ("The Error is" & adoerr.Number & " " & adoerr.Description)
Next
End If
End With
End Sub
Thanks!
-------------------------
Sub PurgeFundAmountRecs()
'This sub will remove all FundAmount records that are older than 4 years from
'from the FundAmounts table.
On Error GoTo PurgeFundAmts_Error
Dim SqlStr As String
Dim DeleteYear As String
Dim conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim adoerr As ADODB.Error
DeleteYear = Year(Date) - 5
Set conn = CurrentProject.Connection
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = conn
SqlStr = "DELETE FROM [FundAmounts] WHERE FundAmounts].YearID <= " & DeleteYear
Cmd.CommandText = SqlStr
Cmd.CommandType = adCmdText
Cmd.Execute RecordsAffected:=recs, Options:=adExecuteNoRecords
Set Cmd = Nothing
MsgBox ("FundAmounts table has been successfully purged"
Exit Sub
PurgeFundAmts_Error:
With conn
If .Errors.Count > 0 Then
For Each adoerr In conn.Errors
MsgBox ("The Error is" & adoerr.Number & " " & adoerr.Description)
Next
End If
End With
End Sub