Access Database Design and Programming, Second Edition

Data Definition Languages

We have already mentioned that a DBMS needs to provide a method for defining new databases. This is done by providing a data definition language, or DDL, to the programmer. A DDL is not a procedural language; that is, its instructions do not actually perform operations. Rather, a DDL is a definitional language.

The Jet Data Definition Language

Example 7.1 illustrates the use of the Jet data definition language. The code will run in Visual Basic or in an Access code module, so feel free to key it in and try it yourself. (Use a new database in Access, since some of this code will conflict with the LIBRARY database that we have been working with in earlier chapters.) The purpose is to create a new database called LIBRARY, along with a table called BOOKS, containing two fields, ISBN and TITLE, and one index. (Don’t worry if some portions of this code don’t make sense to you at this point.) Note that Access uses a space followed by an underscore character ( _ ) to indicate that the next line is a continuation of the current line.

Example 7-1. Use of the Jet Data Definition Language

' Data Definition Language example

' Declare variables of the required types
Dim ws As Workspace
Dim dbLibrary As Database
Dim tblBooks As TableDef
Dim fldBooks As Field
Dim idxBooks As Index

' Use the default workspace, called Workspaces(0)
Set ws = DBEngine.Workspaces(0)

' Create a new database named LIBRARY 
' in the default Workspace
Set dbLibrary =  _ 
ws.CreateDatabase("d:\dao\library.mdb", ...