DB2 9 and Microsoft Access 2007: Working with your DB2 Data in Access 2007

April 28, 2008

In the first part of this series, I showed you how to use Access 2007 as a graphical front end to a back-end DB2 data server. Specifically, I showed you how to implement linked tables and create an abstraction layer over those linked tables such that information workers can work with business artifacts directly without worrying about different naming conventions; all the while, the data resides in a DB2 data server and there is virtually no hit to productivity. In this article, I want to delve deeper into what you can do with the data that resides in those linked tables, add more tables to our Access 2007 front end to show how to leverage DB2 as an integration layer, and demonstrate some of the business rules that Access 2007 maintains when presenting DB2 data to information workers.

Before you start

This series assumes that you’ve linked the DatabaseJournalAccess2DB2 Access 2007 database created in Part 1 to the ORG, EMPLOYEE, STAFF, DEPT, and INVENTORY tables in the DB2 SAMPLE database and created an abstraction layer over those tables such that the All Tables view looks like this:

If you need help getting to this point, check out “DB2 9 and Microsoft Access 2007 Part 1: Getting the Data...”.

Working with DB2 data in Access 2007

At this point, you’ve got some linked tables set up for a number of tables that reside in the SAMPLE DB2 database. As previously mentioned, you can easily see any of the data these tables hold by double-clicking the table in the All Tables view. In the following figure, I opened the Staff business object, which links to the STAFF table in the SAMPLE database:

As you can see in the previous figure, Access 2007 returns the data for a given table in a native Microsoft data grid. This provides you with a usable way in which to work with your data.

Note: The term data grid refers to the Access 2007 object that houses a data set. A data set is the set of data that is returned by a query, stored procedure, linked table, and so on. A data grid is used to display a data set to information workers in Access 2007. In this article, I refer to these terms. When I refer to a data grid, I’m referring to any of the operational controls that Access 2007 allows you to perform; for example: . When I refer to the data set, I mean data; for example, in the previous figure, is part of the data set.

Consider the result set from the query SELECT * FROM STAFF when run from the DB2 command line processor (DB2 CLP) against the SAMPLE database:

You can easily add a record to the STAFF table, through the Staff linked table, using Access 2007. To add a record to a data set, scroll down to the bottom of an Access 2007 data grid, locate the empty cell, and use it to add a new row. An asterisk (*) denotes this input row. For this example, add a new row as follows:

Notice that when you begin to edit a row, an edit icon () appears in the left margin of the data grid of the row you are editing. This icon denotes that you are changing or adding data to a data set (in this case, the STAFF table).

You can alter existing data in the data set’s rows as well. For example, for employee Gafney, change his job to Mgr by overwriting Clerk. You can put focus on this row by clicking any field in the row and Access 2007 will highlight the row to show you that the focus has changed.

When you have finished making changes to a row, simply click elsewhere in the data grid and shift focus away from the row you just changed (or added).

Note: If you press Enter while editing a field, Access 2007 changes focus to the next field in the row.

The color of the margin in the data grid changes according to the action you are performing. By default, the whole row is selected. (An orange line surrounded the entire row when you selected it in the previous figure.) When you change a specific column, the orange focus surrounds the field being changed and the margin marker () turns orange () to show you have changed focus on a specific field in a specific row. As you change the data, the margin marker will show the change icon again ().

To commit any changes you make, click the , as shown below:

If you ran the same query in the DB2 CLP, you would see these changes. (I’m showing you the results in the DB2 CLP so you can see that changes are occurring; of course, you could simply reopen the data grid to see them as well):

If you’re a database administrator (DBA) and you’re reading this article, you may be having a bit of a fit right now imagining all sort of devious data corruption incidents that you’re going to be on the hook for. You can relax: Access 2007 keeps the authorization profiles that are assigned to users in DB2. In other words, if you don’t have write access to a table (or if a table is removed) then you won’t be able to write to it.

In the following figure, you can see that I gave Chloe access to the STAFF table, but only for read operations:

Now if user Chloe uses Access 2007 to see data in the STAFF table, she will be able to view the data, but won’t be able to make any of the changes I detailed above. For example, if Chloe tried to make the following change to the ORG table:

Access 2007 would return the following error messages:

You can also see that Chloe doesn’t have access to the ORG table either (which she sees as Organization in Access 2007):

If she tries to access this table through Access 2007, she will receive the following error message:

This authorization enforcement is dynamic: a DBA’s changes to the authorization semantics for a given set of database objects are effective immediately.

Besides altering and inserting data, you can delete data from the data grid as well if you have the right privileges:

Now if you look at the STAFF table you would see that this row no longer exists:

But you can see that the change made to employee 350 is still here, which shouldn’t be surprising.

Access 2007 will honor advanced table features such as generated columns, sequences, and so on. For example, create a table called COMPLAINTS using the following DDL (substitute your own schema for PAULZ where appropriate):

CREATE TABLE PAULZ.COMPLAINTS(COMPLAINTNUMBER SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1  
MINVALUE +1 MAXVALUE +32767  NO CYCLE CACHE 20 NO ORDER), 
LASTNAME VARCHAR(50) NOT NULL,
COMPLAINT CLOB(1048576) NOT LOGGED NOT COMPACT);