ADO - Delete Records in ADO

1. Ways to Delete Records in ADO

There are three main methods:

  1. Using Recordset.Delete → Deletes the current record in an open recordset.

  2. Using Connection.Execute → Runs an SQL DELETE query directly.

  3. Using Command Object → Deletes records using parameterized queries or stored procedures.

2. Method 1: Using Recordset.Delete

This method is used when you have fetched records and want to delete specific rows interactively.

Syntax

rs.Delete
rs.Update

Example in VB

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Open recordset with write permissions
rs.Open "SELECT * FROM Employees", conn, 1, 3  ' adOpenKeyset, adLockOptimistic

' Move to first record and delete it
If Not rs.EOF Then
    rs.Delete
    rs.Update
    MsgBox "First record deleted successfully!"
Else
    MsgBox "No records found!"
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

3. Method 2: Using Connection.Execute (Recommended for Bulk Deletes)

This is the fastest and simplest way to delete data. You directly execute an SQL DELETE statement.

Example in VB

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Delete all employees with salary below 30000
conn.Execute "DELETE FROM Employees WHERE Salary < 30000"

MsgBox "Records deleted successfully!"

conn.Close
Set conn = Nothing

Advantages:

  • Faster than Recordset.Delete.

  • Best for deleting multiple rows at once.

  • Less memory usage.

4. Method 3: Using Command Object (Best for Secure Deletes)

Using Command objects with parameters prevents SQL injection and is better when dealing with user inputs.

Example in VB

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Configure command
Set cmd.ActiveConnection = conn
cmd.CommandText = "DELETE FROM Employees WHERE EmployeeID = ?"
cmd.CommandType = 1  ' adCmdText

' Add parameter
cmd.Parameters.Append cmd.CreateParameter("EmployeeID", 3, 1, , 5)  ' adInteger

' Execute command
cmd.Execute

MsgBox "Employee deleted successfully!"

conn.Close
Set cmd = Nothing
Set conn = Nothing

5. Example: ADO Delete in ASP Classic

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Delete employees in IT department
conn.Execute "DELETE FROM Employees WHERE Department='IT'"

Response.Write "Records deleted successfully!"

conn.Close
Set conn = Nothing
%>

6. Example: ADO Delete in VBScript

Dim conn
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Delete record by ID
conn.Execute "DELETE FROM Employees WHERE EmployeeID = 10"

WScript.Echo "Employee deleted successfully!"

conn.Close
Set conn = Nothing

7. Best Practices for ADO Delete

  • Always confirm before deleting records.

  • Use WHERE in your DELETE statements to avoid removing all data accidentally.

  • Use transactions for safety when deleting multiple records:

    conn.BeginTrans
conn.Execute "DELETE FROM Employees WHERE Salary < 25000"
conn.CommitTrans

  • Use parameterized queries with the Command object when working with user input.

  • Close Recordsets and Connections properly to free resources.