Opening Recordsets from within VBA
- Using DAO (Data Access Objects)
· The DBEngine Object
- The DBEngine object represents the Jet Engine (or ODBCDirect if you are connecting to a remote enterprise server). The DBEngine object is really only used to access Errors collection and the Workspaces collection.
· The Workspace Collection
- The Workspace Collection is usually used when Access database has multiple users. Each Workspace object represents a single instance of a user interacting with the database engine.
· The Database Object
- The Database object contains five collections
- Containers – represents the collection of Container objects, each of which contains Documents collection. This collection is used when programmatically managing database security.
- QueryDefs – represents the collection of currently open recordsets in the database (including internal queries created during table or report creation).
- Recordset – represents the collection of currently open recordsets in the database.
- Relations – represents the collection of relationships between tables in the database. This collection is rarely used.
- TableDefs – represents the collection of table (including system tables).
· The OpenRecordset Method
- The Recordset object equal to the return value of the Database object’s
OpenRecordset method.
· Navigating through the Recordset
o MoveNext – moves the cursor to the next record in the recordset.
o MovePrevious – moves the cursor to the previous record in the recordset.
o MoveFirst - moves the cursor to the first record in the recordset.
o Movelast - moves the cursor to the last record in the recordset.
o Move <x> - moves the cursor forward x number of records. If x is positive, the cursor position moves forward that many records. If x is negative, the cursor position moves backward that many records. If x is zero, the underlying data for the current record is retrieved.
- BOF - When the cursor is pointing at the beginning of the file (BOF), it is actually set before the first record.
- EOF - the opposite of BOF. When the cursor is pointing at the end of the file (EOF), it is actually set after the last record.
· The RecordCount Property
- The RecordCount property is used to return the number of records in the recordset.
- Using ADO (Using ActiveX Data Objects)
- ADO is the most current data access technology from Microsoft. ADO provides a common interface to data by developing an object model that is loosely formed and designed to provide as much or as little functionality that a data provider would need to expose to an application.
Note: For most everyday tasks within you Access-VBA applications, DAO will still have the edge, both in terms of performance and functionality. If you are working with Access tables and VBA and have no intention of upsizing to client-server database such as SQL Server, the staying with DAO is currently the best option. If you are using Access as a front-end to client-server database in an Access project, you should use ADO.
· Data Providers
- A data provider is a mechanism that connects to a physical data source. ADO, through OLE DB, has access to these data providers and thus we can connect to different types of data source.
- The OLE DB providers supplied with ADO 2.1 include:
- Microsoft Jet 4.0 OLE DB Provider – for Access 2000 databases
- Microsoft OLE DB Provider for Oracle – for Oracle databases
- Microsoft OLE DB Provider for SQL Server – for accessing SQL Server
databases
· The Connection Object
- The connection object represents the connection to a data source.
- The ConnectionString property of the Connection object is used to specify the full connection information required to connect to a data source.
· The Recordset Object
- The Recordset object is used for manipulating a resultset from an OLE DB provider.
· Navigating through the Recordset
o MoveNext – moves the cursor to the next record in the recordset.
o MovePrevious – moves the cursor to the previous record in the recordset.
o MoveFirst - moves the cursor to the first record in the recordset.
o Movelast - moves the cursor to the last record in the recordset.
o Move <x> - moves the cursor forward x number of records. If x is positive, the cursor position moves forward that many records. If x is negative, the cursor position moves backward that many records. If x is zero, the underlying data for the current record is retrieved.
- BOF - When the cursor is pointing at the beginning of the file (BOF), it is actually set before the first record.
- EOF - the opposite of BOF. When the cursor is pointing at the end of the file (EOF), it is actually set after the last record.
Post a Comment