Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

Share this Page URL


260 Microsoft Access VBA Programming for the Absolute Beginner, Fourth Edition TIP A common problem in beginning ADO programming is troubleshooting connec- tion errors. One frequent error is to overlook the path and filename passed to the Data Source property or Open method. Make sure these values match cor- rectly with the location, name, and version of your database. Regardless of your connection choice, you should always close your ADO connections using the Connection object's Close method. The ADO Close method frees application resources, but does not remove the object from memory. To remove objects from memory, set the object to Nothing . In general, connections should be opened once when the application is first loaded (in an object's Load event, for example) and closed once when the application is closing (in an object's UnLoad event, for example). W ORKING WITH R ECORDSETS The ADO programming model uses recordsets to work with rows in a database table. Using ADO recordsets, you can add, delete, and update information in database tables. The Recordset object represents all rows in a table or all rows returned by a SQL query. The Recordset object, however, can refer to only a single row of data at time. Once a database connection has been established, Recordset objects can be opened in one of three ways: · Using the Open method of the Recordset object. · Using the Execute method of the Command object. · Using the Execute method of the Connection object. The most common way of opening recordsets is through the Open method of a Recordset object. Recordset object variables are declared like any other variable--using the ADODB library: Dim rsEmployees As New ADODB.Recordset Once a Recordset object variable has been declared, you can use its Open method to open a recordset and navigate through the result set. The Open method takes five arguments: rsEmployees.Open Source, ActiveConnection, CursorType, LockType, Options Before moving further into recordsets, let's investigate the concept of database locks and cursors. Moreover, let's consider the relationship between locks and cursors with result sets and ADO's Recordset object.