DB2 9 and Microsoft Access 2007: Working with your DB2 Data in Access 2007 - Page 2
April 28, 2008
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 its 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 wasnt 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 Im 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 Ive 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 Ive 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 Im 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 Ive detailed so far in this article:
Heres an example of a linked table thats 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.