Results 1 to 1 of 1

Thread: Database - ADO Tutorial for Classic VB

  1. #1

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005

    Arrow Database - ADO Tutorial for Classic VB

    ADO stands for ActiveX Data Objects. 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:
    1. Start Visual Basic
    2. Choose the "Standard EXE" option and click OK
    3. Add 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:
      Name:  Reference.JPG
Views: 34279
Size:  40.9 KB
    4. 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:
        Name:  Form.JPG
Views: 33488
Size:  9.4 KB
    5. 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:
    1. Option Explicit
    2. Private cn As ADODB.Connection  'this is the connection object
    3. 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 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:
    1. Private Sub Form_Load()
    2.     'turn MousePointer to HourGlass to show that we are busy processing
    3.     Me.MousePointer = vbHourglass
    5.     'instantiate the connection object
    6.     Set cn = New ADODB.Connection
    7.     'specify the connectionstring
    8.     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    9.                           "Data Source=" & App.Path & "\DB1.mdb"
    10.     'open the connection
    11.     cn.Open
    13.     'instantiate the recordset object
    14.     Set rs = New ADODB.Recordset
    15.     'open the recordset
    16.     With rs
    17.         .Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    19.         'loop through the records until reaching the end or last record
    20.         Do While Not .EOF
    21.             Combo1.AddItem rs.Fields("field1")
    22.             rs.MoveNext 'moves next record
    23.         Loop
    25.         If Not (.EOF And .BOF) Then
    26.             rs.MoveFirst    'go to the first record if there are existing records
    27.             FillFields      'to reflect the current record in the controls
    28.         End If
    30.     End With
    32.     Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
    33. 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:
    1. Private Sub cmdAddNew_Click()
    2.     'Just a simple demo to validate invalid inputs.
    3.     'In this one we will not allow a blank entry for text1
    4.     If Text1.Text = "" Then
    5.         MsgBox "Please specify value for text1!   ", vbExclamation, "Invalid Input"
    6.         Text1.SetFocus 'return focus to Text1
    7.         Exit Sub
    8.     End If
    10.     rs.AddNew 'adding new record
    11.     rs.Fields("field2") = Text1.Text  'setting field2 = whatever is typed in text1
    12.     rs.Fields("field3") = Text2.Text  'as above
    13.     rs.Fields("field1") = Combo1.Text 'as above
    14.     rs.Update 'this updates the recordset
    15. 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:
    1. Private Sub cmdDelete_Click()
    2.     'determine if the recordset has an existing record or not before confirming the deletion, 'that should be more user-friendly
    3.     If Not (rs.BOF = True Or rs.EOF = True) Then
    4.         'Confirm if you really want to delete this record.
    5.         If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete?") = vbYes Then
    6.             rs.Delete
    7.             'Credits goes to brucevde for sharing this fix
    8.             rs.MoveNext 'Attempt to move to the next record
    9.             If rs.EOF Then 'check if the last record was deleted
    10.                 'then attempt to move to a previous record.
    11.                 'note that if there are no more records (ie RecordCount is now 0) BOF will get set to True as well
    12.                 rs.MovePrevious
    13.             End If
    14.             FillFields
    15.         End If
    16.     Else
    17.         MsgBox "No record is existing, Delete not allowed!   ", vbExclamation, "No Record"
    18.     End If
    19. End Sub

    This moves to the next record.
    VB Code:
    1. Private Sub cmdNext_Click()
    2.     'check if there are records to move
    3.     If Not (rs.BOF = True And rs.EOF = True) Then
    4.         rs.MoveNext 'move to next record
    5.         'if we did not reach the last record then show it
    6.         If Not rs.EOF Then
    7.             FillFields  'fill the controls
    8.         Else
    9.             rs.MoveLast 'go to the last record
    10.             MsgBox "Last record reached!   ", vbExclamation, "Status"
    11.         End If
    12.     Else
    13.         MsgBox "No record is existing, Move Next not allowed!   ", vbExclamation, "No Record"
    14.     End If
    15. End Sub

    This moves to the previous record.
    VB Code:
    1. Private Sub cmdPrev_Click()
    2.     'check if there are records to move
    3.     If Not (rs.BOF = True And rs.EOF = True) Then
    4.         rs.MovePrevious 'move previous record
    5.         'if we did not reach the first record then show it
    6.         If Not rs.BOF Then
    7.             FillFields      'fill the controls
    8.         Else
    9.             'if we are not on the last record then MoveFirst
    10.             If Not rs.EOF Then
    11.                 rs.MoveFirst 'go to the first record
    12.             End If
    13.             MsgBox "First record reached!   ", vbExclamation, "Status"
    14.         End If
    15.     Else
    16.         MsgBox "No record is existing, Move Previous not allowed!   ", vbExclamation, "No Record"
    17.     End If
    18. End Sub

    This displays the records from our database or resets them if there are no records.
    VB Code:
    1. 'This fills the controls (textbox and combobox) with the current record from the recordset
    2. Public Sub FillFields()
    3.     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.
    4.         Text1.Text = rs.Fields("Field2")            'text1 = field2 and display that data
    5.         Text2.Text = rs.Fields("Field3")            'as above
    6.         Combo1.Text = rs.Fields("Field1")           'as above
    7.     Else
    8.         'reset the textbox and combobox if there are no more records
    9.         Text1.Text = ""
    10.         Text2.Text = ""
    11.         Combo1.Text = ""
    12.     End If
    13. End Sub

    This is the exit point of our demo program.
    VB Code:
    1. Private Sub Form_Unload(Cancel As Integer)
    2.     'Clean-up procedure
    4.     'determine if rs is nothing or not before closing it and setting it to nothing.
    5.     'If under some circumstances that rs has been set to nothing anywhere in the form then
    6.     '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.
    7.     If Not rs Is Nothing Then
    8.         'first, check if the state is open, if yes then close it
    9.         If (rs.State And adStateOpen) = adStateOpen Then
    10.             rs.Close
    11.         End If
    12.         'set them to nothing
    13.         Set rs = Nothing
    14.     End If
    15.     'same comment with rs
    16.     If Not cn Is Nothing Then
    17.         If (cn.State And adStateOpen) = adStateOpen Then
    18.             cn.Close
    19.         End If
    20.         Set cn = Nothing
    21.     End If
    22. 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.

    Attached Files Attached Files
    Last edited by si_the_geek; Jan 6th, 2009 at 06:35 PM. Reason: minor formatting changes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width