Now add COMPLAINTS as a linked table to your Access 2007 database
and add some values to the table. Using the Access 2007 data grid, you can add
new rows, leaving the COMPLAINTNUMBER column empty in the data set since it
will be automatically generated by DB2. When you save the changes and reopen
the data grid, you will see that the generated columns were added to the base
table:
What happens if you tried to specify an explicit value for
the COMPLAINTNUMBER column; well, just as if you tried to specify a value for
this column using an INSERT
statement and the DB2 CLP, you would receive an error in Access 2007 too:
You can do many interesting things with linked tables and
DB2 as your back-end data server. For example, although it’s beyond the scope
of this article to show you how to set the following examples up, I created a
view in DB2 that actually covers an Access 2003 table (that version number
wasn’t a mistake). You can see in the following figure that I exposed this as a
linked table in Access 2007. When I select this object, Access 2007 goes to DB2
and takes the data from what it thinks is a DB2 view but is really a covering
OLE DB function, and then fetches the data from Access 2003. Pretty neat, huh?
While, at first glance, this may seem like an extreme example,
and I’m not suggesting you needlessly hop through layers of an IT
infrastructure to get data, but imagine seeing your DB2 data server as an
integration platform as opposed to just some place to store data. In this
example, you can imagine the benefits: your business users would continue to
simply work with their business artifacts, IT would be satisfied with their
increased controls over the data, and, as more and more disparate systems are
brought together, their ability to centrally manage these systems for control,
governance, and more.
In the following figure, you can see that I’ve added some
more linked tables to our Access 2007 integration front end from Access 2003
and Excel 2003 – all managed from DB2 and exposed through Access 2007:
As I’ve previously stated, it makes no difference which DB2 data
server you are connecting to. In the following figure, I added a table that
resides on DB2 for z/OS, but note that the data I’m trying to add violates a
NOT NULL condition on a column in this table:
Of course, you can read from DB2 for z/OS data servers and
perform all the actions I’ve detailed so far in this article:
Here’s an example of a linked table that’s connected to a multidimensional
clustering (MDC) table that resides in DB2 for AIX and contains 10,000 records
dimensionally organized around two dimensions: DIVISION and DEPARTMENT:
Access 2007 provides a number of native data grid features to work with your data sets.
For example, Access 2007 has built-in
order operators:
It even supports composite filtering whereby data values are
first ordered by the qualifying column followed by a subsidiary-ordering
column:
You can see in the previous figure that Access 2007
considers a NULL value to be larger than a defined value so it subsequently
orders those rows without values for the YEARS column before others, and then
applies an order to the SALARY column.
» 
Comment and Contribute
