To explain ADO data handling in code, we’ll build an ADO project. This application lets you open the db1.mdb file of access using ADO objects to edit records, add, delete records. You can move through the database using arrow buttons you see in fig. To edit a record, just type the new value into the text box and click on update button. To add new record, use add button and insert new values, after it click on update button.
The first step to start the ADO project is give reference to Microsoft ActiveX Data Object library item, and click on OK to add object library to your project
Then create object of Connection and Recordset Dim Cn As New ADODB.Connection Dim Rs As New ADODB.Recordset
Now we are free to create a new ADO connection to object with the Connection object’s Open method: Connction.Open ConnectionString[, UserID [, Password [, OpenOption]]]
Here are the arguments for this method.
Connection String-String containing connection information.
UserID- String containing a username to use when establishing the connection Password – String containing a password to use when establishing the connection. OpenOptions - If set adConnectAsync, the connection will be opened asynchronously. Code
Private sub form_load()
dim cn as new ADODB.Connection
cn.open “PROVIDER = Microsoft.Jet.OLEDB.3.51;Data source = “C:\db1.mdb;” ….
…. End sub
Creating a Recordset from Connection
Now that’s you have created an ADO connection, you open a record set from that connection using the Recordset object’s Open method
Recordset.Open [ Source, [ Active_connection , [Type, [ LockType, [ options]]]] Here the arguments for this method
Source- A valid command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of Recordset.
ActiveConnection- A valid Connection object variable name or a string containing ConnectionString parameter.
Type- Sets the recordset type (see the following list)
• dbopenKeyset: Opens a dynaset-type recordset object, which is like an ODBC keyset cursor.
• dbOpenDynamic: Opens a dynamic-type Recordset object, which lets the application see changes made by other
• dbOpenStatic: Open a static type recordset object.
• dbOpenForwardOnly: Opens a forward-only-type Recordset object, where you can only use MoveNext to move.
LockType: A value what determines what type of locking the provider should use when opening the record set.
Here are the possible values for the LockType: adLockReadOnly: the default; readonly.
adLockPessimistic: Pessimistic locking, record by record. adLockOptimistic: Optimistic locking, record by record.
adLockBatchOptimistic: Optimistic batch updates. e.g.
Private sub form_load()
dim cn as new ADODB.Connection dim rs as new ADODB.Recordset
cn.open “PROVIDER = Microsoft.Jet.OLEDB.3.51;Data source = “C:\db1.mdb;” rs.Open “Select * from lib”, cn , adOpenStatic,adLockOptimistic
…. End sub
ADO: Binding Controls to Record sets
To bind a control to ADO Recordset objects, you just set that controls DataSource property to that object, and then set whatever other data properties that control needs to have set
e.g.
Private sub Form_load()
Dim cn as new ADODB.Connection Dim rs as new ADODB.Recordset
cn.open “PROVIDER = Microsoft.Jet.OLEDB.3.51; Data source = “C:\db1.mdb;” rs.Open “Select * from lib”, cn , adOpenStatic,adLockOptimistic
set text1.datasource = adorecordset text1.Datafield = “Title”
set text2.datasource = adorecordset text2.Datafield = “cost”
End sub
ADO: Adding a Record to a Recordset
To add a new record to an ADO recordset, you use the AddNew method, after you’ve updated the fields of the current record, you save that record to the database with the update method. here’s how you use AddNew:
Recordset.AddNew [ Fields [, Values]]
Fields: a single name or an array of names or ordinal positions of the fields in the new record.
Values: A single value or an array of values for the fields in the new record. If Fields is an array, values must also be an array with the same number of members.
e.g.
rs.AddNew
ADO: Updating A Records in a Record Set.
After changing the data in a record’s in fields or adding a new record, you update the data source to record the changes, using the Update method:
Recordset.Update fields,values
Here are the arguments for this method:
Values - A single value or an array of values for the fields in the new record. If fields is an array, Values must also be an array with the same number of members.
e.g. rs.Update
ADO: Navigating a Recordset:
After creating a Recordset object, the various methods can be used to navigate through the recordset.
• The MoveFirst : Method moves to the first row in the Recordset
• The MoveLast : Method moves to the Last row in the Recordset
• The MoveNext : Method moves to the Next row in the Recordset
• The MovePrevious : Method moves to the Previous row in the Recordset Using BOF and EOF to navigate through Recordset
The Recordset object provides two properties for the user to know when he has moved to the beginning or end of the recordset
The EOF (End of File) property is True when the user moves beyond the last record in the recordset.
The BOF (Beginning of file) property is true when the user moved to a position before the first record in the recordset.
Modifying and Deleting Records
To manipulate a recordset, the following methods are used. Edit Method:
The user can edit the current record using the edit method. The Update method is used to save the necessary changes made to the records.
AddNew Method: AddNew method is used to add a blank record in the recordset.
Delete Method: This method can be used to delete record in the dynaset-type recordset. The Jet-engine deletes the current record without any warning when the Delete method used.