A recordset is a window the database opens into a query result. Some windows are sealed shut – you only look. Some you can reach through and edit. Some only let you walk forward through the rows. Knowing which window you’re holding is the whole game.
the basics What a recordset actually is
When your application asks the database a question – “give me all customers in Reston who bought something this month” – the database doesn’t just hand you a flat list. It hands you a recordset: a structured set of rows and columns plus a small bookkeeping layer that tracks where you are in the result and what you’re allowed to do with it.
The same idea shows up under different names depending on the technology. In classic DAO (Data Access Objects, the long-running database layer in Microsoft Access and older Visual Basic) it’s literally called a Recordset. In ADO (its successor) the type is also called Recordset. SQL Server result sets, ODBC cursors, and even the rows you iterate over in a modern PHP or Python query are all variations on the same theme.
the five types Five recordsets, five trade-offs
The classic DAO model defines five recordset types. Each one sits at a different point on three axes: read vs. write, live vs. snapshot, and how much the database has to keep tracking for you. The right choice depends on what your screen needs to do – show a quick report, or let users actually edit.
| Type | What it really is | Best for |
|---|---|---|
| Table-type | A direct, editable view of one table. Fastest possible reads and writes because the engine can use the table's own indexes. | read / write |
| Dynaset-type | A live, editable result set built from one or more tables via a query. Stays in sync with the source – other people's edits show up. | live / editable |
| Snapshot-type | A frozen copy of the rows at the moment you asked. Fast to scroll, no surprises – but it doesn't notice if someone else changes the data after. | read only |
| Forward-only-type | A snapshot you can only walk through once, top to bottom. Lightest possible footprint. Perfect for one-pass exports and reports. | read / one-pass |
| Dynamic-type | A live result that picks up new rows added by other users mid-loop. Powerful, expensive – the database has to keep watching the source for changes. | live / heavy |
Table-type – the fastest one
A table-type recordset is bound directly to a single base table. Because the engine can use that table’s indexes, opening a row by primary key is essentially instantaneous. Writes go straight back to the source. The catch: it’s only available against tables in the database you’re connected to – you can’t open a table-type recordset across a join.
Dynaset-type – the everyday workhorse
A dynaset is the result of a query, kept live. If another user updates a row that’s in your dynaset, your next read of that row reflects their change. You can edit rows in a dynaset; the changes propagate back to the underlying tables. Almost every “list with editing” admin screen you’ve ever used was a dynaset under the hood.
Snapshot-type – the report’s best friend
A snapshot is exactly what it sounds like: a static copy of the result rows, made once, held in memory. Scrolling forward and backward is fast because nothing is being re-fetched. The trade-off is freshness – your snapshot doesn’t see edits made by other users after it was taken. Use it for reports, exports, and any read-only screen where consistency-of-view matters more than absolute freshness.
Forward-only-type – the one-pass workhorse
Forward-only is the leanest possible recordset: a snapshot you can only iterate through once, top to bottom, with no ability to scroll back. The engine can stream rows to you as it produces them, holding very little state. Use it for nightly exports, mailing-list runs, batch jobs – anywhere you read each row exactly once and never need to come back.
Dynamic-type – the rare one
A dynamic recordset is the most powerful and the most expensive: it not only reflects edits to the rows it already contains, it also picks up new rows that match its query while the recordset is still open. Implementing this requires the database engine to keep watching the source. In practice, you reach for it only when you genuinely need a live, growing view of changing data – most “live” feeling UIs today get there with periodic refresh, not with a dynamic recordset.
in practice Picking the right one
The decision isn’t theoretical. The wrong recordset type can quietly cripple a screen, and you usually find out only after real users have been suffering through it for a while.
where it shows up today The same idea, with new names
The classic five-type taxonomy is from the DAO/ADO era, but the underlying ideas show up everywhere modern. Server-side cursors in SQL Server map directly: STATIC is a snapshot, DYNAMIC is a dynamic recordset, KEYSET is the close cousin of a dynaset, and FAST_FORWARD is forward-only. ORM frameworks like Doctrine, Eloquent, or Django’s ORM hide the choice behind their own APIs, but every “lazy collection” you can iterate is, underneath, one of these patterns.
Knowing the names matters because the constraints don’t go away just because the API changes. A lazy iterator that streams rows once is forward-only by nature – you’ll get an error if you try to walk it twice. A query result you load into a list and edit in memory is, effectively, a snapshot – your edits won’t propagate unless you explicitly write them back. The vocabulary is older than today’s frameworks; the trade-offs are exactly the same.
summary Five questions, five answers
- Will users edit this data on the screen? If yes, dynaset or table. If no, snapshot or forward-only.
- Does it matter if other people’s changes appear mid-session? If yes, dynaset or dynamic. If no, snapshot.
- Will the user scroll back through the results? If no, forward-only is the cheapest possible answer.
- Is the result set large enough to matter for memory? If yes, lean toward forward-only or table – snapshots can get heavy.
- Are you joining tables? If yes, you can’t use a table-type recordset – go dynaset or snapshot.