Using ADO to Communicate with the Database, Part 1
ADO provides developers with a powerful, logical object model for programmatically accessing, editing, and updating data from a wide variety of data sources. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint.
To use the objects you must make a reference to them in your project. Go to Project/References and check the highest version of Microsoft ActiveX Data Objects. This could be 2.0 or 2.6.To start the communication with the database, there must be some procedure, which we need to follow. To start with, we need to declare a Connection Object.
What is a Connection?
A Connection object represents a physical connection to a database. When you open the Connection object, you attempt to connect to the database. The State property of the Connection object tells you whether you succeeded or failed. You can send SQL statements or run stored procedures by using the Execute method of the Connection object. If the command you send to the data store returns records, a Recordset object will be created automatically.
Referencing the Connection
Before you proceed, you need to refer the ADO (Microsoft Activex Data Object ver X.XX) in your project. To add the reference you need to go to the Project Menu and then select the Microsoft Activex Data Object X.XX.
Declaring a Connection
Before establishing a session with the database, you need to declare the Connection. Connection can be declared like any of the method below:
Dim objConn As ADODB.Connection Set objConn = New ADODB.Connection
Or
Dim objConn As New ADODB.Connection Opening a Connection
To open a Connection, we must make a connection string (Specifying Provider, Path of the database, User ID and Password etc) with the Database. At a minimum, you'll require to specify a Provider/connection string which indicates the type of the database, and a Data Source String, which is simply the path and file name of your database. To separate an Individual setting=value combinations of the connection string are separated with semi-colons.
Dim sConnect As String ‘ Declarign the Connection sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:databasemyaccessfile.mdb" objConn.Open sConnect ‘ Opening the Connection
Provider for MS-Access 2000 Database file is 'Microsoft.Jet.OLEDB.4.0 Provider for MS-Access 97 Database its 'Microsoft.Jet.OLEDB.3.51.
Once our connection is established, we can read the records (Rows) of the table by using the ADODB Recordset object.
As name suggests, ‘Recordset’ is nothing but a set of records from a base table or the results of an executed command. At any time, the Recordset object refers to only a single record (row) within the set as the current record. The data which the recordset will provide us, is dependent on the Query/Table/Stored Procedure we provided to open the recordet. When you use ADO, you manipulate data almost entirely using Recordset objects. All Recordset objects consist of records (rows) and fields (columns). Depending on the functionality supported by the provider, some Recordset methods or properties may not be available
Declaring and Opening a Recordset
Dim objRs As ADODB.Recordset Set objRs = New ADODB.Recordset
A database element that controls record navigation, updateability of data, and the visibility of changes made to the database by other users. There four different types of cursor available in ADO.
Dynamic cursor — allows you to view additions, changes, and deletions by other users; allows all types of movement through the recordset
Keyset cursor — behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete. Data changes by other users will still be visible. Allows all types of navigation through the recordset.
Static cursor — provides a static copy of a set of records for you to use to find data or generate report. Additions, changes, or deletions by other users will not be visible.
Forward-only cursor — allows you to only scroll forward through the Recordset. Additions, changes, or deletions by other users will not be visible.
If multiple users try to update the same record at the same time, an error will occur. When locking your records, you will either be optimistic (adLockOptimistic) that this error won't occur, or you will be pessimistic (adLockPessimistic) and figure this error is likely to happen. So we require to implement some locking strategy. So Lock Type is required because, it determines how to implement concurrent sessions, specially when the solution is supposed to run in a network environment. How to lock the records when the application will run on a network. In optimistic locking, other users are only locked out of the record(s) when the Update method is called - probably just a split second. In pessimistic locking, other users are locked out for the entire period that the records are being accessed and modified.
Navigating Through a Recordset?
Recordset is a collection of one or more rows from the database and table specified by you. So you must be able to navigate (Move) through this collection of rows. The Move method moves to a record in a database specified by a row number.
Move: The Move method moves to a record in a database specified by a row number (e.g adoRS.Move 4 will move the recordset to fourth record)
MoveFirst: Moves to the first record in the recordset
MoveLast: Moves to the last record in the recordset
MovePrevious: The MovePrevious method moves to the previous record in the recordset. The BOF property should be checked to prevent an error occurring. BOF is set to True if the current record is before the first record in the recordset
MoveNext: The MoveNext method moves to the next record in the recordset. The EOF property should be checked to prevent an error occurring. EOF is set to True if the current record is after the last record in the recordset
Now we will be creating a simple Database Enabled form which will allow you add, display, edit, and delete the records. To start with, we will create a simple form which will store the name, address, and telephone number of an employee.
Step-I (Design)
1. Open Visual Basic and Create a New .EXE project and save it as AddBook.Vbp
2. When u will create a project, a form named “Form1.frm” will be added automatically in the project, Change the name of he Form as “frmMyAddrBook.frm”
3. Place Three Label and change the caption to “Name”, “Address” and Teleephone Number” respectively
4. Place Three TextBox on the form and Name it as “txtName”, “txtAddr” and “txtTel”
5. Place a ListBox Control on the form and Name it as lstContact
6. Add some Buttons for Adding, Saving, Deleting andClearing the form and name it “cmdAdd”, “cmdSave”, “cmdDelete” and “cmdClear”
Step-II (Referencing the ADO)
1. Goto Project -> Reference Menu
2. Add reference of the “Microsoft Actiovex Data Object [XX.XX] class
1. Double Click the form Window, it will open the Editor Window of “frmMyAddrBook”
2. Declare some Variables in the General Declaration Section of the Code.
Dim objConn As ADODB.Connection ‘ Connection Object Dim objRs As ADODB.Recordset Dim strConnString As String ‘ Variable to Hold the Connection String Dim strQstr As String ‘ Variable to store Query Dim sSelID As Double Dim sOperateMode As Strign
3. Write a procedure to clear the controls on the form
Private Sub ClearControls() ‘ This procedure will clear the controls on the form. TxtName=”” TxtAddr=”” TxtTel=”” End Sub
4. Call this procedure from ther Clear Button Click Event
Private Sub cmdClear_Click() ClearControls ‘ it can be called like Call ClearControls End Sub
5. Establish Connection with The database
Private Function bgetConnected()As Boolean On Error goto ConnErr GetConnectedb=False Set objConn = New ADODB.Connection StrConnStr= "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=” App.path & “mydeb.mdb" Objconn.Open StrConnStr bGetConnected=true Exit Function ConnErr: Msgbox “Unable to Connect to the Database becaue” & Err.Description, VbInformation End Function
6. Loading the Already Existing data to the List
7. Now Call This procedure in the Form_Load Event so that data will remain blank
Private Sub Form_Load() Call ClearContriols ‘ Calling procedure to clear the input controls SOperateMode=”” BgetConnected ‘ Establishing Connection with the Database End Sub
8. Add the Code below to Add Button Click Event
Private cmdAdd_Click() Call Clearcontrols ‘ Clear Data Operatemode=”Add” ‘ Seting Operation Mode End Sub
9. Add a procedure to validate the Data, like Name and Address Must be entered before we save the Data.
Private Function bValidateData() As Boolean BValidateData=False ‘ Default we r assuming that Data is Invalid If (Trim(txtName)=”” Then Msgbox”Please Enter Name”,Vbinformation,App.title Trim(txtName.Setfocous ‘Seting focus to Name filed Exit Function Endif ‘’’ Place similar Validation for Address here ‘ .... .... ... BValidateData=True End Function
10. Now We need to Save the data, so we will be writing a single procedure which will save the Data (Add/Edit/Delete)
Private bSaveData() As Boolean On Error goto ErrHandlr ‘ incorporating Error Handler BSaveData=false StrQstr=”” If sOperateMode=”Add” Then StrQstr=”Insert Into tblAddrBook (Name, Addr,Tel) Values(“ StrQstr=strQstr & “’” & txtName & “’,” & txtAddr & “’,’” & txtTel &”’’)”
Else If sOperateMode=”Del” Then StrQstr=”Delete From tblAddrBook Where conID=” & Val(sSelID) Endif ‘ Now We will Use the Connection Object’s Execute Method to Run Our Query objConn.Execute strSql bSaveData=true Exit Function
ErrHandlr: Msgbox “Unable to Save The Data Because “ & Vbcrlf & Err.Description , vbinformation, app.title
End Function
11. Now We Need to Call the Function to Save The data
Private cmdSave_Click() If bSaveData Then ‘ If data has been Saved Successfully Then Give a message
Msgbox “Contact has been Added Succefully” Call PopulateData ‘ Refreshing the List of already Added data End Sub
12. Now we will be deleting the data Selected by the User
Private cmdDelete_Click() If Val(sSelId)=0 Then ‘Use has not selected any Data to delete Msgbox “Please select a Contact to Delete” LstContact.setfocous Exit Sub End if If bSaveData Then Msgbox “the Contact has been deleted” Call ClearControls ‘ Clear the Controls Call PopulateData ‘ repopulate The data End if
End Sub
13. Now we need to write a procedure to populate the data into the list Box.
Private Function PopulateData() Dim dctr As double Objrs.activeConnection=ObjConn Objrs.LockType=adLockOptimistic Objrs.CursorType=adOpenStatic Objrs.CursorLocation=adUseClient StrQstr=”Select * from tblAddrBook Order By Name” Objrs.open StrQstr,,,,adcmdtext If objrs.RecordCount>0 Then For dCtr=1 to Objrs.Recordcount LstContact.addItem Objrs!Name ‘ Displaying Name Only ‘Populating ID in the Item Data LstContact.ItemData(lstContact.NewIndex)=Objrs!Id ‘ Objrs.movenext Nect dCtr Endif If objrs.state=1 Then ‘ If Connection is Open Objrs.close Endif
End function
14. Again we need a procedure to Display the Contact Information as the user selects a contact from the List Box
Private Function DispData() Objrs.activeConnection=ObjConn Objrs.LockType=adLockOptimistic Objrs.CursorType=adOpenStatic Objrs.CursorLocation=adUseClient StrQstr=”Select * from tblAddrBook Where ID=”& Val(selID) Objrs.open StrQstr,,,,adcmdtext If objrs.RecordCount>0 Then TxtName= Objrs!Name ‘ Displaying Name TxtAddr= Objrs!Name TxtTel= Objrs!Tel Endif If objrs.state=1 Then ‘ If Connection is Open Objrs.close Endif
End Function
15. Now We have to write some code to display the detail information in the input controls, when user double clicks a contact in the List box_ Add The Code below into the Double_Click Event of the lstContact
Private Sub lstContact_DoubleClick(Byval Index as integer) SelID=lstContact.itemdata(lstContact.ListIndex) Call DispData ‘ Calling Function to display the contact information End Sub
Database Code 
/ 37 
What is ADO?
