Databases Reference
In-Depth Information
7. Database System Architecture
7.1 Why Program?
There is no doubt that SQL is a powerful language—as far as it goes. However, it is a
somewhat unfriendly language, and it lacks the sophisticated control structures of a more
traditional language, such as
For...Next...
loops and
If...Then...
statements.
This is not really a problem, since SQL is designed for a very specific purpose related to
database-component creation and manipulation. SQL is not designed to provide an
overall programming environment for Microsoft Access itself. This role is played by
Visual Basic for Applications
(VBA).
VBA is the macro or scripting language for all of the major Microsoft Office products:
Microsoft Access, Excel, PowerPoint, and Word (starting with Word 97). It is a very
powerful programming language that gives the programmer access to the full features of
these applications, as well as the means to make the applications work together.
One of the major components of VBA is its support for
Data Access Objects
model,
(DAO). DAO is the programming-language interface for the Jet database management
system (DBMS) that underlies Microsoft Access. It provides a more-or-less object-
oriented data definition language (DDL) and data manipulation language (DML), thereby
allowing the VBA programmer to define the structure of a database and manipulate its
data.
Of course, it is natural to wonder why you would want to use DAO, and VBA in general,
rather than using the built-in graphical interface of Microsoft Access. The answer is
simple. While the graphical interface is very easy to use and is quite adequate for many
purposes, it is simply not as powerful as the programming languages. The database
creator gains more power and flexibility over the database by directly manipulating the
basic objects of the database (such as the tables, queries, relationships, indexes, and so
on) through programming.
As a simple example, there is no way to get a list of the fields of a given table (i.e, the
table's
table scheme
) using the Access graphical interface. However, this is a simple
matter using programming techniques. The following short program:
Sub Example( )
Dim db As DATABASE
Dim tdf As TableDef
Dim fld As Field
Set db = CurrentDb
Set tdf = db.TableDefs("BOOKS")
For Each fld In tdf.Fields
Debug.Print fld.Name
Next
