CodeItBetter Programming Another VB Programming Blog

'Database - How to Determine how many records were inserted by an INSERT ... SELECT statement
Option Explicit
 
'Add a reference to: Microsoft ActiveX Data Objects 2.6 Library

Private Sub cmdCountInsertedRecords_Click()
    Dim strDbFile As String
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim intRecordCount As Integer
 
    ' Get the data.
    strDbFile = App.Path
    If Right$(strDbFile, 1) <> "\" Then strDbFile = strDbFile & "\"
    strDbFile = strDbFile & "people.mdb"
 
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDbFile & _
        ";" & "Persist Security Info=False"
    conn.Open
 
    ' Empty the TempNames table.
    conn.Execute "DELETE FROM TempNames", , adCmdText
 
    ' Copy records from Names into TempNames.
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = "INSERT INTO TempNames (ID, FirstName, LastName) " & _
        "SELECT ID, FirstName, LastName FROM PeopleNames"
    cmd.Execute intRecordCount
 
    MsgBox "Inserted " & intRecordCount & " records."
 
    conn.Close
End Sub