ADO - ADO Recordset

1. What is ADO Recordset?

An ADO Recordset is an object in ActiveX Data Objects (ADO) used to store, retrieve, and manipulate data from a database.
When you execute a query using ADO, the results are returned inside a Recordset.

2. Purpose of Recordset

  • Fetches data from the database.

  • Allows navigation through rows.

  • Allows adding, editing, or deleting records.

  • Can work in connected or disconnected mode.

3. Creating a Recordset

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

After creating it, you open it using the Open method:

rs.Open Source, ActiveConnection, CursorType, LockType, Options

Parameters:

  • Source → SQL query or table name.

  • ActiveConnection → Connection object or connection string.

  • CursorType → How you navigate through data.

  • LockType → Controls editing and locking of records.

  • Options → Tells ADO if the source is text, table, or stored procedure.

4. Cursor Types in Recordset

CursorType Constant Description
0 adOpenForwardOnly Default. Moves forward only, fastest.
1 adOpenKeyset Can move both ways; sees other users' updates but not new records.
2 adOpenDynamic Can move both ways and sees all changes by others.
3 adOpenStatic Snapshot of data. Does not see others' changes.

5. Lock Types in Recordset

LockType Constant Description
1 adLockReadOnly Default. Cannot modify data.
2 adLockPessimistic Locks the record as soon as editing begins.
3 adLockOptimistic Locks the record only when updating.
4 adLockBatchOptimistic Used for batch updates in disconnected mode.

6. Common Recordset Methods

Method Description
Open Opens the recordset.
Close Closes the recordset.
MoveFirst Moves to the first record.
MoveLast Moves to the last record.
MoveNext Moves to the next record.
MovePrevious Moves to the previous record.
AddNew Adds a new record.
Update Saves changes to the current record.
Delete Deletes the current record.

7. Common Recordset Properties

Property Description
Fields Collection of columns in the recordset.
BOF Returns True if before the first record.
EOF Returns True if after the last record.
RecordCount Returns the total number of records.
State Indicates whether the recordset is open or closed.

8. Example: Reading Data from Recordset (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
rs.Open "SELECT * FROM Employees", conn, 1, 3

' Loop through data
Do While Not rs.EOF
    Debug.Print rs.Fields("FirstName") & " " & rs.Fields("LastName")
    rs.MoveNext
Loop

' Close objects
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

9. Example: Adding a New Record

rs.Open "Employees", conn, 1, 3
rs.AddNew
rs.Fields("FirstName") = "John"
rs.Fields("LastName") = "Smith"
rs.Update
MsgBox "New record added!"
rs.Close

10. Example: ASP Classic

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

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

rs.Open "SELECT * FROM Employees", conn, 1, 3

Do While Not rs.EOF
    Response.Write rs("FirstName") & " " & rs("LastName") & "<br>"
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

11. Best Practices

  • Always close recordsets after use.

  • Use forward-only cursors for better performance when only reading data.

  • Use disconnected recordsets for efficiency in web apps.

  • Always check for EOF and BOF before accessing data.

  • Implement error handling when opening and updating records.