Visual Basic 6.0
linked to Access
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
Toolbox Pull-down menus Tool bar Form
Immediate window Form layout window
Setting up the ADO Data
The References option on the
Project menu must be set
To the latest version of the
MS ADO Library
Setting up the ADO Data
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 Final screen
Record Source Screen
Click on the expansion button on the
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
• E.g. to display the value of a field from the DB in a
– add a text box to the form
– Go to properties window
– set the DataSource to the name of the ADO
– set the DataField to the required field in the
• 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
• 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
• These commands can be added to click events of buttons or
even a menu:
If (NOT Adodc1.Recordset.EOF) Then
Else MsgBox (“Already at last record - Cannot move
• 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
• To delete a record the following VB command
can be used:
– 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
– 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
• To Update the current record
– Update only performed when current record
changes (e.g. with MoveNext or