10 – Recordsets

In this post, we will be taking a look at Recordsets.

Strictly speaking a Recordset is an object available to VBA and Access that encapsulates the functionality and code necessary to interact with the Jet Database Engine and any other data source available via ODBC.  In simple terms it lets you play with data held in tables in Access.

You already know there are tables that contain data in Access, but did you know that VBA cannot directly access these tables? Tables are stored in an arcane fashion quite unlike anything in VBA and we need assistance from the Recordsets object to gloss over the issues surrounding accessing the data.

List of topics covered:

• Introduction: What are Recordsets
• Checking DAO is Referenced
• Checking ADO is Referenced
• Adding Missing DAO and ADO References
• Adding DAO References
• Adding ADO References
• Again, Check DAO and ADO References
• Declaring a Recordset Object
• DAO Recordsets
• ADODB Recordsets
• DAO vs ADODB
• Getting Recordsets with CurrentDb (DAO)
• Getting Recordsets with CurrentProject (ADODB)
• Cursors
• Locks
• Lock Types
• Recordsets and SQL
• Opening a Recordset
• Counting Records
• Looping Through a Recordset
• While and Until Loops
• For Each
• Backwards Through the Records
• Editing Records (and the With Keyword)
• Deleting Records
• Adding Records
• Closing Recordsets
• Common Errors
• Using Form.Recordsets
• Removing Recordset Objects
• Record Locking
• Assuming Recordset Operations Will Work
• Dirty Reads and Buried Updates
• Batch Updating

Downloadable Content

Code Examples VBA Fundamentals

PDF

Videos

References

Opening A Recordset

Simple Recordsets

Related Posts