If you want to get the number of records that were updated, inserted, or deleted by your query, you would read the RecordsAffected property on the object that executed your query after it had run.

'Copied from a normal module
'We set a reference to the CurrentDb object once so that we can pass this to the ExecuteSQL function.
'Then the ExecuteSQL does an arDB.Execute sSQL at some point and then uses
'arDB.RecordsAffected to get that number of records.

Public Function ExecuteSQL_GetRecordsAffected(sSQL As String) As Long
    Dim arDB As DAO.Database
    Set arDB = CurrentDb
    ExecuteSQL sSQL, arDB
    ExecuteSQL_GetRecordsAffected = arDB.RecordsAffected
    Set arDB = Nothing
End Function

So the above function was written to use another function, ExecuteSQL which takes an SQL string and an optional DAO database. Here is that function:

Public Sub ExecuteSQL(SQL As String, Optional ByRef dbCurrent As DAO.Database)
   Dim SetDbToNothing As Boolean
   If dbCurrent Is Nothing Then
      Set dbCurrent = CurrentDb
      SetDbToNothing = True
   End If
   Dim MultipleStatements As Variant
   Dim SQLele As Variant
   If InStr(SQL, ";") <> 0 Then MultipleStatements = SplitSQLStatements(SQL) Else MultipleStatements = Array(SQL)
   For Each SQLele In MultipleStatements
      If SQLele <> "" Then dbCurrent.Execute SQLele, dbSeeChanges
   Next SQLele
   If SetDbToNothing Then Set dbCurrent = Nothing
End Sub

I wrote this so I could handle multiple SQL statements separated by semicolons. I am using an additional helper function called SplitSQLStatements to make sure I’m not splitting on semicolons inside of single quotes, but there are other situations where semicolons could be used (maybe in a SQL comment or if you are using parameters in your SQL statement maybe) Here is the SplitSQLStatements function:

Public Function SplitSQLStatements(sqlString As String) As Variant
    Dim sqlArray() As String
    Dim currentStatement As String
    Dim inString As Boolean
    Dim char As String
    Dim i As Integer
    Dim statements As Collection
    
    Set statements = New Collection
    currentStatement = ""
    inString = False
    
    For i = 1 To Len(sqlString)
        char = Mid(sqlString, i, 1)
        
        ' Check if we are entering or exiting a string
        If char = "'" Then
            inString = Not inString
        End If
        
        ' If we encounter a semicolon and we are not inside a string, split here
        If char = ";" And Not inString Then
            statements.Add Trim(currentStatement)
            currentStatement = ""
        Else
            currentStatement = currentStatement & char
        End If
    Next i
    
    ' Add the last statement if there is any
    If Trim(currentStatement) <> "" Then
        statements.Add Trim(currentStatement)
    End If
    
    ' Convert the collection to an array
    ReDim sqlArray(1 To statements.Count)
    For i = 1 To statements.Count
        sqlArray(i) = statements(i)
    Next i
    
    SplitSQLStatements = sqlArray
End Function

Now let’s say you want to run a Passthrough Query against SQL Server and want to get the RecordsAffected. In that case you cannot use this method. You must use either a second query on the same connection to get the recordcount using special SQL Server syntax, or you need to create a stored procedure in SQL Server that will return the number of affected records. For more information on this see this article on StackOverflow:

https://stackoverflow.com/questions/39705203/dao-recordsaffected-does-not-return-the-right-value-after-passthru-merge