Databases Reference
In-Depth Information
Database
Index
BOF
Dynamic array
Index
a
c
d
1
2
3
4
5
1
2
3
a
b
c
d
e
EOF
FIGURE 8.2
Using pointers for populating dynamic arrays.
database are to be extracted to an array because they meet some specified criteria, the indexes of
the database and the indexes of the array will not correspond because some items in the database
will not be extracted to the array, depending upon the criteria of the query. In such an instance, it
is necessary to utilize a pointer to point to the next available index in a dynamically sized array.
Also keep in mind that if a combo box or list box object is to be populated, the first index is 0.
The first index of an array will be dependent upon the setting of the Option Base command. In the
absence of the Option Base statement, the lower limit index of an array is set to 0.
When looking at the coding examples in this chapter of the text, it is necessary for the reader to
keep these concepts in mind. It is also very useful when debugging code in database applications where
unexpected results have occurred, to print the elements extracted and their corresponding indexes in
both the database and the array they have been populated into. This makes finding errors much easier.
8.3
CONCEPTS IN DATABASE ALTERATION AND MANAGEMENT
Certain concepts in database management and alteration transcend the various means of implemen-
tation. In this text, database management will be implemented using examples in SQL and DAO,
and certain terms and commands will be utilized using both schemes, although the syntax may differ.
The three most important concepts in database management and alteration are that of commit,
update, and rollback. Whenever a change is made to a record in a database, the record must be updated
for the change to be recorded. Updating is nothing more than changing the data in a database table.
When a series of changes is made to a database, they are not implemented until the commit
command is issued. Commit finalizes all the changes made in the database since the last commit
command was issued.
The rollback command is essentially the opposite of the commit command. Issuing the rollback
command undoes any changes made to the database using the update command since the last
commit command was issued.
These concepts are utilized when dealing with the Workspace object introduced in Chapter 7.
Examples:
BeginTrans
begins a new transaction.
CommitTrans
ends the current transaction and saves the changes.
Rollback
ends the current transaction and restores the databases in the Workspace object
to the state they were in when the current transaction began.
8.4
CREATING NEW TABLES IN ACCESS FROM EXCEL
Effective database alteration and maintenance begin at the table level. To effectively maintain a
database, a programmer must be able to accomplish the following from Excel: (1) Determine which
Tables already exist, (2) determine which Tables are System Tables (that is, tables created and
utilized by Access), (3) create New Tables in an existing database using either DAO or SQL.
