Data Access Objects DBEngine Object The DBEngine object

Data Access Objects

DBEngine Object • The DBEngine object contains and controls all other objects in the hierarchy of DAO objects.

DBEngine Object-Methods Name Description Begin. Trans Begins a new transaction. Read/write Database. Commit. Trans Ends the current transaction and saves the changes. Create. Database Creates a new Database object, saves the database to disk, and returns an opened Database object (Microsoft Access workspaces only). . Create. Workspace Creates a new Workspace object. Open. Connection Opens a Connection object on an ODBC data source (ODBCDirect workspaces only). Open. Database Opens a specified database and returns a reference to the Database object that represents it. Rollback Ends the current transaction and restores the databases in the Workspace object to the state they were in when the current transaction began.

DBEngine Object-Properties Name Login. Timeout Description Sets the password used to create the default Workspace when it is initialized. Read/write String. Sets or returns a value that indicates what type of workspace will be used by the next Workspace object created. Sets the user name used to create the default Workspace when it is initialized. Read/write String. Returns an Errors collection that contains all of the stored Error objects for the specified object. Read-only. Sets or returns information about the Windows Registry key that contains values for the Microsoft Access database engine (Microsoft Access workspaces only). Sets or returns the number of seconds before an error occurs when you attempt to log on to an ODBC database. Properties Returns the Properties collection of the specified object. Read-only. Version Rreturns the version of DAO currently in use. Read-only String. Returns a Workspaces collection that contains all of the active, unhidden Workspace objects. Read-only. Default. Password Default. Type Default. User Errors Ini. Path Workspaces

Database object • You use the Database object and its methods and properties to manipulate an open database.

Database object-Methods Name Close Description Closes an open Database. Create. Property Create. Query. Def Creates a new user-defined Property object (Microsoft Access workspaces only). . Creates a new Query. Def object. Create. Table. Def Creates a new Table. Def object (Microsoft Access workspaces only). New. Password Runs an action query or executes an SQL statement on the specified object. Changes the password of an existing Microsoft Access database engine database (Microsoft Access workspaces only). Open. Recordset Creates a new Recordset object and appends it to the Recordsets collection. Execute

Database object-Properties Name Connection Name Query. Defs Records. Affected Recordsets Table. Defs Transactions Updatable Description Sets or returns a value that provides information about the source an open database. Read/write String. Returns the Connection object that corresponds to the database (ODBCDirect workspaces only). Returns the name of the specified object. Read-only String. Returns a Query. Defs collection that contains all of the Query. Def objects of the specified database. Read-only. Returns the number of records affected by the most recently invoked Execute method. Returns a Recordsets collection that contains all of the open recordsets in the for the specified database. Read-only. Returns a Table. Defs collection that contains all of the Table. Def objects stored in the specified database. Read-only. Returns a value that indicates whether an object supports transactions. Read-only Boolean. Returns a value that indicates whether you can change a DAO object. Read-only Boolean.

Recordset object-Methods A Recordset object represents the records in a base table or the records that result from running a query. Name Description Add. New Creates a new record for an updatable Recordset object. Clone Creates a duplicate Recordset object that refers to the original Recordset object. Closes an open Recordset. Edit Copies the current record from an updatable Recordset object to the copy buffer for subsequent editing. Find. First Locates the first record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only). Find. Last Locates the last record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

Recordset object-Methods Name Get. Rows Move Description Retrieves multiple rows from a Recordset object. Moves the position of the current record in a Recordset object. Move. First Moves to the first record in a specified Recordset object and make that record the current record. Move. Last Moves to the last record in a specified Recordset object and make that record the current record. Move. Next Moves to the next record in a specified Recordset object and make that record the current record. Move. Previous Moves to the previous record in a specified Recordset object and make that record the current record. Requery Updates the data in a Recordset object by re-executing the query on which the object is based. Seek Locates the record in an indexed table-type Recordset object that satisfies the specified criteria for the current index and makes that record the current record (Microsoft Access workspaces only). Update Saves the contents of the copy buffer to an updatable Recordset object.

Recordset object-Properties Name BOF Bookmark EOF Fields Filter Index Last. Modified Last. Updated No. Match Record. Count Updatable Description Returns a value that indicates whether the current record position is before the first record in a Recordset object. Read-only Boolean. Sets or returns a bookmark that uniquely identifies the current record in a Recordset object. Returns a value that indicates whether the current record position is after the last record in a Recordset object. Read-only Boolean. Returns a Fields collection that represents all stored Field objects for the specified object. Read-only. Sets or returns a value that determines the records included in a subsequently opened Recordset object (Microsoft Access workspaces only). Read/write String. Sets or returns a value that indicates the name of the current Index object in a tabletype Recordset object (Microsoft Access workspaces only). Returns a ookmark indicating the most recently added or changed record. Returns the date and time of the most recent change made to a base table. Readonly Variant. Indicates whether a particular record was found by using the Seek method or one of the Find methods (Microsoft Access workspaces only). Returns the number of records accessed in a Recordset object, or the total number of records in a table-type Recordset object. or Table. Def object. Read-only Long. Returns a value that indicates whether you can change a DAO object. Readonly Boolean.

Recordset. Type. Enumeration Name Value Description db. Open. Dynamic 16 Opens a dynaset-type Recordset db. Open. Dynaset 2 Opens a dynaset-type Recordset db. Open. Forward. Only 8 Opens a forward-only type Recordset db. Open. Snapshot 4 Opens a snapshot-type Recordset db. Open. Table 1 Opens a table-type Recordset

Function Find. Record. Count(str. SQL As String) As Long Dim dbs. Northwind As DAO. Database Dim rst. Records As DAO. Recordset On Error Go. To Error. Handler Set dbs. Northwind = Current. Db Set rst. Records = dbs. Northwind. Open. Recordset(str. SQL) If rst. Records. EOF Then Find. Record. Count = 0 Else rst. Records. Move. Last Find. Record. Count = rst. Records. Record. Count End If rst. Records. Close dbs. Northwind. Close Set rst. Records = Nothing Set dbs. Northwind = Nothing Exit Function Error. Handler: Msg. Box "Error #: " & Err. Number & vb. Cr. Lf & Err. Description End Function

Dim dbs. Northwind As DAO. Database Dim rst. Employees As DAO. Recordset Set dbs. Northwind = Current. Db Set rst. Employees = dbs. Northwind. Open. Recordset("Employees") rst. Employees. Move. First Do Until rst. Employees. EOF If rst. Employees!Title = "Sales Representative" Then rst. Employees. Edit rst. Employees!Title = "Account Executive" rst. Employees. Update End If rst. Employees. Move. Next Loop

Query. Def object-Methods A Query. Def object is a stored definition of a query in a Microsoft Access database engine database. Name Close Description Closes an open Query. Def. Create. Property Creates a new user-defined Property object (Microsoft Access workspaces only). Executes an SQL statement on the specified object. Open. Recordset Creates a new Recordset object and appends it to the Recordsets collection.

Query. Def object-Properties Name Connect Description Sets or returns a value that provides information about the source of database used in a pass-through query. Readonly String. Fields Returns a Fields collection that represents all stored Field objects for the specified object. Read-only. Max. Records. Affected Sets or returns the maximum number of records to return from a query against an ODBC data source. Returns the number of records affected by the most recently invoked Execute method.

Table. Def object-Methods • A Table. Def object represents the stored definition of a base table or a linked table (Microsoft Access workspaces only). Name Create. Field Create. Index Create. Property Open. Recordset Refresh. Link Description Creates a new Field object (Microsoft Access workspaces only). . Creates a new Index object (Microsoft Access workspaces only). . Creates a new user-defined Property object (Microsoft Access workspaces only). Creates a new Recordset object and appends it to the Recordsets collection. Updates the connection information for a linked table (Microsoft Access workspaces only).

Table. Def object-Properties Name Description Sets or returns a value that provides information Connect about a linked table. Read/write String. Returns a Fields collection that represents all stored Field objects for the specified object. Read. Fields only. Returns or sets the name of the specified object. Name Read/write String. Returns the total number of records in Record. Count a Table. Def object. Read-only Long. Sets or returns a value that specifies the name of a Source. Table. Nam linked table or the name of a base table (Microsoft e Access workspaces only).