Databases Reference
In-Depth Information
FIGURE 7.6
Compound primary key designation for Products Table.
The Products Table is somewhat more problematic. Although assigning a single field for a primary
key is desirable, it is not always possible. The Products Table represents just such a case. Here, many
products may have the same manufacturer, and some manufacturers might even duplicate model
names or model numbers. The only way to guarantee uniqueness is by utilizing a plurality of keys.
In this instance, if both the MANUFACTURER and the MODELNUMBER are jointly referenced,
they must produce a unique row entry. No manufacturer will have two products that utilize the same
model number. Doing so would make it impossible for the manufacturer to then know what the
consumer desired, defeating the whole purpose of having a model number to begin with.
Therefore, both MANUFACTURER and MODELNUMBER must be designated as primary
keys. Setting multiple primary keys is also easy. The user must view the table in Design View and
choose the fields (shown as rows) to be designated as primary keys, and click on them holding
down the CTRL key. Holding down the CTRL key will allow the user to select multiple rows.
Once multiple rows have been selected, the user simply either right-clicks and selects “Primary
Key” from the menu or presses the primary key button on the Access toolbar. When compound or
multiple primary keys are selected, a key will appear next to each field designated as a subset
constituting the primary key as shown in Figure 7.6.
7.3
CONNECTING TO AN MS ACCESS DATABASE
Whether the user builds the database or wishes to access a database constructed by means of a third
party, a mechanism must be put in place which allows Excel to access the database from the VBA
environment. For Excel to access a database, it needs the following information about the database:
1.
The database name (For MS Access a *.mdb file name)
2.
The location of the database (the path to the *.mdb file)
Excel can obtain this information by utilizing a Data Source Name (DSN). A DSN provides
connectivity to a database through an ODBC driver. ODBC stands for Open DataBase Connectivity
and is an interface for ODBC drivers. Many applications utilize ODBC drivers to allow other
programs to access their data. The DSN contains, at a minimum, the database name, directory, and
database driver, and may contain other information such as a UserID and password. Once a DSN
is created for a particular database, the DSN can be utilized by any application (that supports
DSN/ODBC) to query information from the database.
To access information in a database, the application must utilize software components called
drivers. Data Sources (ODBC) is instrumental in adding and configuring these drivers. To open
Data Sources (ODBC) from the Start menu, choose Settings, then click Control Panel. Double-
click Administrative Tools, and then double-click Data Sources (ODBC). Figure 7.7 shows the
ODBC Data Source Administrator.
