Delete, Append, Update Counts

[tcb]

1) I setwarnings false, run a bunch of delete and append queries,
setwarnings true. Can I capture the values: how many records were
deleted or appended?

2) When running an update query I'd also like a count of records
updated and list:

Name: "Smith" updated to "Johnson."
Status: 0 updated to 1

 

[Guest]

If you use the Execute method of the Database object, then you can retrieve
the number of Records / Rows affected using the RecordsAffected Property
after each Query execution.

In addition, you don't need to use SetWarnings with Execute as Execute is
processed silently without warnings. You can also trap the error using the
dbFailOnError option.

Check Access VB Help on the Execute method of the Database object.

 

[Pieter Wijnen]

Not in a Macro, you can't

Public Function RunQuery(ByVal inSQL As String, Optional ByVal ODBCConnect
As String=VBA.vbNullString) As Long
Dim RQDb As DAO.Database
Dim RQQDef As DAO.QueryDef
Dim thQ As String
Dim oldErr As DAO.Error
Dim Parm As DAO.Parameter

On Local Error Resume Next

Set oldErr = DAO.Errors(0)
VBA.Err.Clear

Set RQDb = Access.CurrentDb()
Set RQQDef = RQDb.QueryDefs(inSQL)
If VBA.Err.Number <> 0 Then ' Temp Query
Set RQQDef = RQDb.CreateQueryDef(VBA.vbNullString)
With RQQDef
If VBA.Len(ODBCConnect) > 0 Then
.ReturnsRecords = False
.Connect = ODBCConnect
End If
.SQL = inSQL
End With
End If
VBA.Err.Clear
With RQQDef
If VBA.Len(ODBCConnect) = 0 Then
For Each Parm In .Parameters
Parm.Value = Access.Eval(Parm.Name) 'Assumes Parameters refer to
Forms Controls
Next
End If
VBA.Err.Clear
If VBA.Len(ODBCConnect) = 0 Then
.Execute DAO.dbSeeChanges + DAO.dbConsistent + DAO.dbFailOnError
Else
.Execute
End If
RunQuery = .RecordsAffected
End With
If ShowErr And VBA.Err.Number <> 0 Then
ODBCError
VBA.Err.Clear
Else
If VBA.Err.Number <> 0 Then
Debug.Print "[RunQuery]", inSQL
Debug.Print VBA.Err.Number, VBA.Err.Description
End If
If Not oldErr Is Nothing Then
VBA.Err.Raise oldErr.Number, oldErr.Source, oldErr.Description ' Keep
Error State
End If

End If
RQQDef.Close: Set RQQDef = Nothing
Set RQDb = Nothing

End Function

HTH

Pieter