# Visual Basic > Visual Basic FAQs >  Database - ADO Tutorial for Classic VB

## dee-u

ADO stands for *A*ctiveX *D*ata *O*bjects. This technology is used to connect to databases and manipulate records stored in those database. This tutorial presents the basic operations of ADO.

To get started here are the steps:
Start Visual BasicChoose the "Standard EXE" option and click OKAdd a reference to "Microsoft ActiveX Data Objects 2._x_ Library". Goto to Project menu then select the References sub-menu. Here's a figure of how the References dialog box looks like:
Add the following controls in your form:
4 Command buttons (cmdAdd, cmdDelete, cmdNext and cmdPrev)2 text boxes(text1, text2)1 combo box(combo1).
It should look like this:
Use the codes listed below.


The two ADO objects we are going to use for this demo are the Connection object and Recordset object. The Connection object is used to connect and communicate with databases like MS Access, SQL Server and Oracle to name a few and in this tutorial we are going to use MS Access as our database. The recordset object is used to retrieve and manipulate records in our database, with the Recordset object we could Add, Update and Delete records and this tutorial will show you how.

VB Code:
Option Explicit
Private cn As ADODB.Connection  'this is the connection object
Private rs As ADODB.Recordset   'this is the recordset object

This is the entry point of our program. It is where we are instantiating our ADO objects, setting their parameters and opening them. The Connection objects' most important property or parameter is the ConnectionString, this is where we specify how we are going to connect and to what database. For a good resource of different Connectionstrings used to connect to different databases have a look at www.connectionstrings.com. The recordset object has different parameters that we have to supply, for a detailed explanation of the those parameters have a look at this FAQ Article: What do the parameters of the recordset.Open method mean?.

VB Code:
Private Sub Form_Load()
    'turn MousePointer to HourGlass to show that we are busy processing
    Me.MousePointer = vbHourglass
    
    'instantiate the connection object
    Set cn = New ADODB.Connection
    'specify the connectionstring
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\DB1.mdb"
    'open the connection
    cn.Open
    
    'instantiate the recordset object
    Set rs = New ADODB.Recordset
    'open the recordset
    With rs
        .Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable
           
        'loop through the records until reaching the end or last record
        Do While Not .EOF
            Combo1.AddItem rs.Fields("field1")
            rs.MoveNext 'moves next record
        Loop
        
        If Not (.EOF And .BOF) Then
            rs.MoveFirst    'go to the first record if there are existing records
            FillFields      'to reflect the current record in the controls
        End If
        
    End With
    
    Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
End Sub

Here we are adding to the database the values from the two textboxes and the combobox. I added a small routine to disallow adding if there is no value in Text1.

VB Code:
Private Sub cmdAddNew_Click()
    'Just a simple demo to validate invalid inputs.
    'In this one we will not allow a blank entry for text1
    If Text1.Text = "" Then
        MsgBox "Please specify value for text1!   ", vbExclamation, "Invalid Input"
        Text1.SetFocus 'return focus to Text1
        Exit Sub
    End If
    
    rs.AddNew 'adding new record
    rs.Fields("field2") = Text1.Text  'setting field2 = whatever is typed in text1
    rs.Fields("field3") = Text2.Text  'as above
    rs.Fields("field1") = Combo1.Text 'as above
    rs.Update 'this updates the recordset
End Sub

This deletes the current record in our recordset. It checks if there are records before deleting, as such we could tell the user that a delete is invalid if there is no record to delete.

VB Code:
Private Sub cmdDelete_Click()
    'determine if the recordset has an existing record or not before confirming the deletion, 'that should be more user-friendly
    If Not (rs.BOF = True Or rs.EOF = True) Then
        'Confirm if you really want to delete this record.
        If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete?") = vbYes Then
            rs.Delete
            'Credits goes to brucevde for sharing this fix
            rs.MoveNext 'Attempt to move to the next record
            If rs.EOF Then 'check if the last record was deleted
                'then attempt to move to a previous record.
                'note that if there are no more records (ie RecordCount is now 0) BOF will get set to True as well
                rs.MovePrevious
            End If
            FillFields
        End If
    Else
        MsgBox "No record is existing, Delete not allowed!   ", vbExclamation, "No Record"
    End If
End Sub

This moves to the next record.

VB Code:
Private Sub cmdNext_Click()
    'check if there are records to move
    If Not (rs.BOF = True And rs.EOF = True) Then
        rs.MoveNext 'move to next record
        'if we did not reach the last record then show it
        If Not rs.EOF Then
            FillFields  'fill the controls
        Else
            rs.MoveLast 'go to the last record
            MsgBox "Last record reached!   ", vbExclamation, "Status"
        End If
    Else
        MsgBox "No record is existing, Move Next not allowed!   ", vbExclamation, "No Record"
    End If
End Sub

This moves to the previous record.

VB Code:
Private Sub cmdPrev_Click()
    'check if there are records to move
    If Not (rs.BOF = True And rs.EOF = True) Then
        rs.MovePrevious 'move previous record
        'if we did not reach the first record then show it
        If Not rs.BOF Then
            FillFields      'fill the controls
        Else
            'if we are not on the last record then MoveFirst
            If Not rs.EOF Then
                rs.MoveFirst 'go to the first record
            End If
            MsgBox "First record reached!   ", vbExclamation, "Status"
        End If
    Else
        MsgBox "No record is existing, Move Previous not allowed!   ", vbExclamation, "No Record"
    End If
End Sub

This displays the records from our database or resets them if there are no records.

VB Code:
'This fills the controls (textbox and combobox) with the current record from the recordset
Public Sub FillFields()
    If Not (rs.BOF = True Or rs.EOF = True) Then    'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("Field2")            'text1 = field2 and display that data
        Text2.Text = rs.Fields("Field3")            'as above
        Combo1.Text = rs.Fields("Field1")           'as above
    Else
        'reset the textbox and combobox if there are no more records
        Text1.Text = ""
        Text2.Text = ""
        Combo1.Text = ""
    End If
End Sub

This is the exit point of our demo program. 

VB Code:
Private Sub Form_Unload(Cancel As Integer)
    'Clean-up procedure
    
    'determine if rs is nothing or not before closing it and setting it to nothing.
    'If under some circumstances that rs has been set to nothing anywhere in the form then
    'checking its state and closing it would raise an error. A recordset can have different states at a time, it can either be executing and open so we are using a bitwise comparison. Special thanks to si_the_geek for providing this knowledge.
    If Not rs Is Nothing Then
        'first, check if the state is open, if yes then close it
        If (rs.State And adStateOpen) = adStateOpen Then
            rs.Close
        End If
        'set them to nothing
        Set rs = Nothing
    End If
    'same comment with rs
    If Not cn Is Nothing Then
        If (cn.State And adStateOpen) = adStateOpen Then
            cn.Close
        End If
        Set cn = Nothing
    End If
End Sub

For additional guidance have a look at this FAQ Article: ADO Beginners Tutorial, Some Further Steps. And this is a compilation of the different frequently asked questions on ADO.

If you cannot find an answer to your question, please create a new thread in the Database Development forum where other members would be able to help you in your concerns.

Credits:
-Beacon
-si_the_geek
-brucevde

----------

