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 End Sub
displays the following list of fields for the BOOKS table in the Debug window:
ISBN Title PubID Price
This is a good place to discuss the relationship between DAO and SQL. The fact is that DAO both uses SQL and overlaps SQL. That is, there are many commands in DAO that can accept an SQL statement as an argument. For instance, the following VBA code opens a recordset (discussed later in the book) using an SQL statement to define the records in the recordset:
' Get current database Set dbs = CurrentDb( ) ' Write SQL statement strSelect = "Select * FROM Books WHERE Price=10" ' Open recordset using SQL statement Set rsCheap = dbs.OpenRecordset(strSelect)
On the other hand, DAO overlaps SQL in the sense that many actions can be performed using either language. For instance, a table can be created using either the SQL statement CREATE TABLE or the DAO method CreateTable. The choice is up to the programmer.
Our main goal in the remaining portion of this book is to discuss the DAO model. Before doing so, however, we need to set the stage by discussing the overall architecture of a database management system, and of the Jet DBMS in particular, so we can put DAO in its proper context. We will do so in this chapter and also take a quick peek at DAO programming. In Part IV, I will present a brief introduction to programming in VBA. Then I will turn to DAO itself in the following chapters of the book. Finally, I’ll conclude by examining ActiveX Data Objects (ADO), Microsoft’s recent technology for universal data access.
A database system is often pictured as a three-level structure, as shown in Figure 7-1.
At the lowest level of the structure is the physical database , which consists of the raw data existing on a physical object, such as a hard disk. At this level, the data has no logical meaning, as related to the database. However, the data does have a very definite physical structure to allow efficient access. In other words, the data is more than just a string of bits.
In fact, there are a variety of structures in which the data might be stored, including hash tables, balanced trees, linked lists, nested records, and so on, and the choice of data structure is not a simple one. However, I will not pursue a discussion of the physical database in this book. Suffice it to say that, at the physical level, the data is viewed as a structured collection of bits, and the sole purpose of the structure is to provide efficient access to the data. The physical level of a database is often referred to as the internal level .
The conceptual database is a conceptual view of the database as a whole. It gives the data a logical structure. For instance, in a relational database system, the data is viewed as a collection of tables, with column headings describing the attributes of the corresponding entity class. Moreover, tables are related to one another through certain columns.
The conceptual model is intended to model the entire database. However, individual users may be interested in views of only specific portions of the data. For instance, in the LIBRARY database, a student using the library’s online database catalog is probably not interested in the price of the book, but is interested in where it is located on the shelves. Thus, a single database, such as LIBRARY, may need different views for the student than for the librarian.
The highest level in the three-tier structure consists of the individual views of the data that may be held by users of the database. Views are also referred to as subschemes, and this level of the tier is also referred to as the external level.
As another example, we can think of the Microsoft Visual Basic programming language as providing an external view of the Jet database management system that is geared toward database programmers. We can think of Microsoft Access as providing an external view that is geared, not just to programmers, but also to high-level users of varying degrees of sophistication. After all, a user does not need to know anything about database programming to create a database in Microsoft Access, although he does need to have a familiarity with the conceptual level of a relational database.
Thinking of a database system as a three-tier structure has distinct advantages. One advantage is that it allows for a certain level of independence that permits the individual tiers to be changed or replaced without affecting the other tiers. For instance, if the database is moved to a new computer system that stores the data in hash tables rather than balanced trees, this should not affect the conceptual model of the data, nor the views of users of the database. Also, if we switch from the Visual Basic view of the database to the Access view, we can still use the same conceptual database model. Put more bluntly, a database table in Visual Basic is still a database table in Microsoft Access.
A DBMS is a software system that is responsible for managing all aspects of a database, at all levels. In particular, a DBMS should provide the following features, and perhaps more:
A mechanism for defining the structure of a database, in the form of a data definition language, or DDL.
A mechanism for data manipulation, including data access, sorting, searching, and filtering. This takes the form of a data manipulation language, or DML.
Interaction with a high-level host language or host application, allowing programmers to write database applications designed for specific purposes. The host language can be a standard programming language, such as C or Visual Basic, or a database application language, such as Microsoft Access.
Efficient and correct multiuser access to the data.
Effective data security.
Robustness—that is, the ability to recover from system failures without data loss.
A data dictionary , or data catalog. This is a database (in its own right) that provides a list of the definitions of all objects in the main database. For instance, it should include information on all entities in the database, along with their attributes and indexes. This “data about data” is sometimes referred to as metadata. The data dictionary should be accessible to the user of the database, so that she can obtain this metadata.
As the title of the book suggests, our primary interest is in the DBMS that underlies Microsoft Access (and also Visual Basic). Accordingly, we will take our examples from this DBMS, called the Jet DBMS or the Jet Database Engine. The relationship between the Jet DBMS and other database-related programs, including Microsoft Access and Visual Basic, can be pictured as in Figure 7-2.
Microsoft’s application-level products Visual Basic, Access, and Excel play host to VBA, which is the underlying programming language (also called scripting or macro language) for these applications. (Microsoft Word Version 7 does not use VBA—it uses a similar language called Word Basic. However, as of Microsoft Word 97, Word does use VBA.) As expected, each of these applications integrates VBA into its environment in a specific way, since each application has a different purpose.
In turn, Visual Basic for Applications is the host language for the Jet DBMS. The Jet DBMS contains the DAO component, which is the programming-language interface for the Jet DBMS. The DAO provides a more-or-less object-oriented DDL and DML, thereby allowing the VBA programmer to define the structure of a database and manipulate its data.
The Jet Database Engine is a collection of components, generally in the form of dynamic link libraries ( DLLs), designed to provide specific functions within the Jet DBMS. (A DLL is essentially a collection of functions for performing various tasks.) The Jet Query Engine handles the translation of database queries into Access SQL, and the subsequent compilation, optimization, and execution of these queries. In short, it handles queries. The Internal ISAM component is responsible for storing and retrieving data from the physical database file. ISAM stands for Indexed Sequential Access Method and is the method by which data is stored in a Jet database file. The Replication Engine allows exact duplicates of a database to coexist on multiple systems, with periodic synchronization.
The host languages for the Jet DBMS, such as Visual Basic and Access, are used by database programmers to create database applications for specific purposes. For instance, we might create a Library database application, which a library can use to maintain information about its books, or an Order Entry database application for a small business.
Incidentally, the Jet DBMS is also capable of interfacing with non-Access-formatted databases, such as those with format Xbase (dBase), Paradox, Btrieve, Excel, and delimited text formats. It can also interface with open database connectivity (ODBC is discussed in Appendix C) to access server database applications across networks.
Let us take a closer look at the components of the Jet DBMS. We will study these components in much greater detail in separate chapters of the book.
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 (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.
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.
' 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:daolibrary.mdb", _ dbLangGeneral) ' Create a new table called BOOKS Set tblBooks = dbLibrary.CreateTableDef
("BOOKS") ' Define ISBN field and append to the ' table's Fields collection Set fldBooks = tblBooks.CreateField
("ISBN", dbText) fldBooks.Size = 13 tblBooks.Fields.Append fldBooks ' Define Title field and append to the ' table's Fields collection Set fldBooks = tblBooks.CreateField
("Title", dbText) fldBooks.Size = 100 tblBooks.Fields.Append fldBooks ' Add the table to the db's Tables collection dbLibrary.TableDefs.Append tblBooks ' Create an index Set idxBooks = tblBooks.CreateIndex
("ISBNIdx") idxBooks.Unique = False ' Indices need their own fields Set fldBooks = idxBooks.CreateField
("ISBN") ' Append to the proper collections idxBooks.Fields.Append fldBooks tblBooks.Indexes.Append idxBooks
As you can see, the clue that we are dealing with a DDL are
the commands CreateDatabase
, CreateTableDef
, CreateField
, and CreateIndex
(in boldface for easier
identification). You can also see from this code that the Jet DBMS
uses the collections to hold the properties of
an object. For instance, the fields that we create for a table must
be appended to the Fields collection for that
table. This has the advantage that we don’t need to keep a separate
reference to each field—the collection does that for us. This
approach is typical of object-oriented programming.
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.
Example 7-2 is Jet DML code to add two records to the BOOKS table, set the index, and display the records.
' Data Manipulation Language example Dim rsBooks As Recordset ' Open the database Set dbLibrary = DBEngine.OpenDatabase("d:daolibrary.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 the query
Data is seldom manipulated without some intended purpose. For instance, consider a LIBRARY database consisting of information about the books in a library. If a student wishes to access this data, it is probably with the intention of finding a certain book, for which the student has some information, such as the title. On the other hand, if a librarian wishes to access the information, it may be for other purposes, such as determining when the book was added to the library or how much it cost. These issues probably don’t interest the student.
The point here is that a DBMS should supply an interface with a high-level language with which programmers can program the database to provide specific services—that is, with which programmers can create database applications. Thus, when a student logs onto a library’s computer to search for a book, he may be accessing a different database application than the librarian might access. The language that is used for database application programming is the hostlanguage for the DBMS. As mentioned earlier, a host language may be a traditional programming language, such as C or COBOL, or it may be an application-level language, such as Microsoft Access or Visual Basic, as it is for the Jet DBMS.
In fact, the Jet DBMS is so tightly integrated into both of these applications that it is hard to tell where one leaves off and the other begins. Put another way, it sometimes seems as though Microsoft Access is the Jet DBMS, whereas it is more accurate to say that Access and Visual Basic are front ends , or host applications , for the Jet DBMS.
The client/server model of a database system is really very simple, but its meaning has evolved somewhat through popular usage. The client/server model is shown in Figure 7-3.
The server in a client/server model is simply the DBMS, whereas the client is the database application serviced by the DBMS. (We could also think of Visual Basic and Access as clients of the Jet DBMS server.)
The basic client/server model says nothing about the location of the various components. However, since the components are distinct, it is common to find them on different computers. The two most common configurations are illustrated in Figures Figure 7-4 and Figure 7-5. The distributed client/server model (Figure 7-4), wherein the client is on one computer and the server and database are on another, is so popular that it is usually simply referred to as the client/server model. The remote database model (Figure 7-5) refers to the case in which the client and server are on the same computer, but the database is on a remote computer.
3.144.40.42