Databases Reference
In-Depth Information
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 topic) 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 topic 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 topic. Finally, I'll conclude
by examining
ActiveX Data Objects
(ADO), Microsoft's recent technology for universal
data access.
7.2 Database Systems
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.
