Access Database Design and Programming, Second Edition

Data Manipulation Languages

A DBMS must also provide a language designed to manipulate the data in a database. This language is called a database manipulation language, or DML. To the database programmer, however, the distinction between a DDL and a DML may be just a logical one, defined more by the purpose of the language than the syntax.

The Jet Data Manipulation Language

Example 7.2 is Jet DML code to add two records to the BOOKS table, set the index, and display the records.

Example 7-2. Jet DML Code Altering the BOOKS Table

' Data Manipulation Language example

Dim rsBooks As Recordset

' Open the database
Set dbLibrary = ws.OpenDatabase("d:\dao\library.mdb")

' Create a recordset for the BOOKS table
Set rsBooks = dbLibrary.OpenRecordset("BOOKS")

' Add two records
rsBooks.AddNew
rsBooks!ISBN = "0-99-345678-0"
rsBooks!Title = "DB Programming is Fun"
rsBooks.Update
rsBooks.AddNew
rsBooks!ISBN = "0-78-654321-0"
rsBooks!Title = "DB Programming isn't Fun"
rsBooks.Update

' Set index
rsBooks.Index = "ISBNIdx"

' Show the records
rsBooks.MoveFirst
MsgBox "ISBN: " & rsBooks!ISBN & "  TI: " & rsBooks!Title
rsBooks.MoveNext
MsgBox "ISBN: " & rsBooks!ISBN & "  TI: " & rsBooks!Title

As you can see even from this small example, the DML is designed to perform a variety of actions, such as:

• Moving through the data in the database

• Adding data to the database

• Editing or updating data in the database

• Deleting data from the database

• Querying the data and returning those portions of the data that satisfy ...