Databases Reference
In-Depth Information
8
From Excel to Access
and Back Again
8.1
INTRODUCTION
This chapter is dedicated to dynamic actions on databases, meaning the algorithms run in Excel
will actually change the content within the database. Two methods will be covered to perform
dynamic actions upon databases within Excel VBA. The first method is to utilize SQL commands,
and the second method is to utilize the DAO model. Both methods were introduced in Chapter 7.
The advantage of using SQL is that both queries and manipulations on databases will occur
much faster than if they had been coded using DAO. SQL commands also have the advantage of
being more powerful, as they can be made as complex as the developer's understanding of the SQL
language. When using the DAO model, the developer is limited to utilizing the properties and
methods contained within the DAO framework. DAO is also self-limiting in that if a developer
learns DAO, database manipulations can only be performed in environments that support DAO.
SQL is the superior method to manipulate databases, its only downside being that one must learn
the SQL language in order to utilize it. SQL is nearly platform independent for basic queries, but
differences do exist between Microsoft Access SQL statements and Oracle SQL statements (in
some instances). In general, learning SQL is usually well worth the time and effort put in up front.
The numerous examples contained within this chapter show how to perform the indicated
operations using
SQL and DAO. It would not be appropriate to exclude either method, and
for the sake of writing nearly platform-independent methods, SQL coverage is a must. DAO is also
worth covering because its simplicity allows developers to put together applications quickly when
time is of the essence.
both
8.2
USING POINTERS IN DYNAMIC DATABASE ALGORITHMS
Any C programmer is familiar with pointers. In C, a pointer is simply a variable that holds a
memory address rather than a value. Because the values of all variables are stored in memory, if
the address of a variable is known, its value can be read from memory without actually accessing
the variable itself. Such a form of access is known as indirect referencing.
Although C formally supports pointers as operators, the fact is that pointers are utilized in
many different algorithms and applications to perform a variety of operations. For all practical
purposes, a pointer can be generically defined as a variable that defines the location of a value of
interest. The location pointed to in the case of a database will be an index or a record number, and
the value of interest will be a single or plurality of values defined by a query.
Writing code to access database elements poses some special programming challenges. Because
databases are dynamic and always changing, the number of records (or rows) in them is in a constant
state of fluctuation. To further complicate matters, individual elements in databases can change
position. The combination of these two factors make using hard-coded For/Next loop structures
impractical.
Figure 8.1 illustrates the challenges that can be encountered when accessing records from a
dynamic database. In this example, a sample database includes five elements (a,b,c,d,e) that are
located in the database in alphabetical order, and thus their record set (or index) numbers are
(1,2,3,4,5), respectively.
279
