• The Data Manager is a versatile utility for creating
and viewing databases. However, its interface is not that pretty and its use is
somewhat cumbersome. We would not want to use it as a database management system
(DBMS). Nor, would we expect users of our programs to have the Data Manager available
for their use. The next step in our development of our database skills is to use
Visual Basic to manage our databases, that is develop a DBMS.
• We will develop a simple DBMS. It will allow us to
view records in an existing database. We will be able to edit records, add records,
and delete records. Such advanced tasks as adding tables and fields to a database
and creating a new database can be done with Visual Basic, but are far beyond
the scope of the discussion here.
• To create our DBMS, we need to define a few more programmatic
control methods associated with the data control Recordset property. These methods
AddNew - A new record is added to the table.
All fields are set to Null and this record is made the current record.
Delete - The current record is deleted from
the table. This method must be immediately followed by one of the Move methods
because the current record is invalid after a Delete.
Update - Saves the current contents of all
• To edit an existing record, you simply display the
record and make any required changes. The LockType property should be set to adLockPessimistic
(locks each record as it is edited). Then, when you move off of that record, either
with a navigation button or through some other action, Visual Basic will automatically
update the record. If desired, or needed, you may invoke the Update method to
force an update (use LockType = asLockOptimistic). For a data control named dtaExample,
the syntax for this statement is:
• To add a record to the database, we invoke the AddNew
method. The syntax for our example data control is:
This statement will blank out any bound data tools and move the current record
to the end of the database. At this point, you enter the new values. When you
move off of this record, the changes are automatically made to the database. Another
way to update the database with the changes is via the Update method.
After adding a record to a database, you should invoke the
Refresh property of the data control to insure proper sorting (established by
RecordSource SQL statement) of the new entry. The format is:
• To delete a record from the database, make sure the
record to delete is the current record. Then, we use the Delete method. The syntax
for the example data control is:
Once we execute a Delete, we must move (using one of the ‘Move’
methods) off of the current record because it no longer exists and an error will
occur if we don’t move. This gets particularly tricky if deleting the last
record (check the EOF property). If EOF is true, you must move to the top of the
database (MoveFirst). You then must make sure there is a valid record there (check
the BOF property). The example code demonstrates proper movement.