Introduction to Visual Basic 6 by hcj


									 Visual Basic 6.0
linked to Access
  Accessing Databases

  The ADO Approach
• A powerful feature of VB is its ability to access
  the contents of various databases.

• It is possible to use the Microsoft Access
  Engine to access, display and alter the
  contents of a database.

• VB can be used to create a front-end interface
  for database tables.
          VB6 Data Access

• 2 Data Object Library approaches are
  available in VB:
  – DAO (Data Access Objects)
    • Original access strategy (up to VB6)
    • Closely linked to MS Access
  – ADO (ActiveX Data Objects)
    • Newer approach
    • More generic
    • Same approach for ASP (VB Script active server
                  The VB Desktop                          Properties window
                                                                     Project window
Toolbox   Pull-down menus                      Tool bar    Form

                            Immediate window                         Form layout window
      Setting up the ADO Data
              Control 1
The References option on the
Project menu must be set
To the latest version of the
MS ADO Library
       Setting up the ADO Data
               Control 2
The Components option
On the Project menu is used
To select the MS ADO
Data Control 6.0 (OLEDB)

This control is added to the
toolbox and can be placed
on a form
   Using the ADO Data Control
   (adodc) to Connect to a DB
• An ADO data control has been attached to the following
• The buttons move the data control to the beginning, end
  and next records in the database.
 Using the ADO Data Control to
        Connect to a DB
• The Connection String & Record Source
  properties must be set
• The Connection String builds the link to
  the database file (.mdb)
• The RecordSource defines the
  table/query to be used from the database
• Using this approach little coding is
• Once the DB and table have been
  identified, the information can be
  displayed on the interface.
ConnectionString screens
ConnectionString Final screen
      Record Source Screen
Click on the expansion button on the
  RecordSource property
Linking Text Boxes to DB Fields

 • Once the ADO data control has been initialised it
   is possible to access the information.
 • No VB code is required, only the use of the
   properties window.
 • E.g. to display the value of a field from the DB in a
   text box:
    – add a text box to the form
    – Go to properties window
    – set the DataSource to the name of the ADO
       data control
    – set the DataField to the required field in the
             Attaching Code
• Before the application can be run, some
  code must be added.
• It is necessary to close the database once
  the application has finished.
 • This is possible by attaching the following
         code to an Exit command button.
     Adodc1.RecordSet.Close     ‘Closes the DB
     End                 ‘Terminates the program
         Manipulating Databases
• In addition to viewing the contents of a database it is also
  possible to add, delete and modify its contents.
• In a user interface it may be the case that the user cannot
  see the data control.
• This can be achieved by setting its properties (visible) to
• VB has a number of reserved words to perform the
  actions of the Data Control:

   –   Adodc1.Recordset.MoveFirst - moves to first record in DB table
   –   Adodc1.Recordset.MoveLast - moves to the last record
   –   Adodc1.Recordset.MoveNext - moves to the next record
   –   Adodc1.Recordset.MovePrevious - moves to the previous record
     Manipulating Databases
• These commands can be added to click events of buttons or
  even a menu:
  Private Command1_Click
       If (NOT Adodc1.Recordset.EOF) Then
       Else MsgBox (“Already at last record - Cannot move
  beyond this!”)
       End If
  End Sub
• The above code tests that the current record is not the last
  record in the DB table - If it is not, the next record is shown.
• Otherwise an error message is shown.
• The command Adodc1.Recordset.BOF refers to the start of a
  DB table.
     Manipulating Databases
• To delete a record the following VB command
  can be used:
  – Adodc1.Recordset.Delete
  – The recordset should then be moved next or previous with
    checks to ensure that EOF or BOF is not being reached
• To add a record (to the end of a table) the
  following commands are required:
  – Adodc1.Recordset.AddNew - creates a new blank record
  – txtENO.SetFocus
  – This sets the focus to the ENO textbox (assuming it is the first)
    to allow new records to be added
  – After entering values the table is updated whenever
   the current record is moved next or previous
     Manipulating Databases
• To Update the current record
  – Adodc1.Recordset.Update
  – Update only performed when current record
    changes (e.g. with MoveNext or
    MovePrevious buttons)

To top