|
|
|
| Title | Determine how many records were inserted by an INSERT ... SELECT statement |
|---|
| Description | This example shows how to determine how many records were inserted by an INSERT ... SELECT statement in Visual Basic 6. This example uses a Command object, passing it a variable where it can record the number of items affected. |
|---|
| Keywords | ADO, data, database, Access, INSERT, INSERT SELECT, SELECT, records, INSERT INTO ... SELECT |
|---|
| Categories | Database |
|---|
|
|
|
Thanks to Ami Dalwadi.
This program connects to a database and deletes the records in the TempNames table. Then it uses an INSERT INTO ... SELECT statement to copy records from the Names table into TempNames. It performs the operation by calling a Command object's Execute method. It passes a variable to the Execute method so the method can return the number of records affected.
|
|
Private Sub cmdGo_Click()
Dim db_file As String
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim num_records As Integer
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & _
"\"
db_file = db_file & "people.mdb"
' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"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 num_records
MsgBox "Inserted " & num_records & " records."
conn.Close
End Sub
|
| |
|
| |
|
|
