Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 21, 2008

DB2 9 and Microsoft Access 2007 Part 1: Getting the Data... - Page 2

By Paul Zikopoulos

After you click Finish, the ODBC IBM DB2 Driver – Add window opens. Simply enter the name of the database, along with its alias and an optional description describing the connection, to the respective fields, and click OK.

You can use the Add button in the previous figure to add a database connection on the fly in context to the action you are performing in Office 2007; that’s a nice part of our integration with the Microsoft API set. For example, let’s assume that your administrator only installed a DB2 Runtime Client on your machine. This means that you don’t have any tools to catalog a database connection. Fear not! The driver is integrated into Microsoft Windows such that many of the options available in the Configuration Assistant previously detailed are available natively through the Windows ODBC framework:

You can see in the previous figure that in you can add a new DB2 data server connection to your system, specify security options, and more; and you can do this in context, so you don’t have to stop what you were doing. You can even add a connection to a DB2 for z/OS or DB2 for i5/OS data server without having to resort to the CLP or another tool. Don’t you love the power of integration?

Even if you like to do everything with scripts, you can use client access profiles, which maintain ODBC information for you. In addition, a number of DB2 command line processor (CLP) commands allow you to interact with the system ODBC settings when cataloging a database. For example:

So long as you have the credentials, you should now be able to link up your DB2 data server to Access 2007. So let’s add some tables. Select the SAMPLE database in the initial Select Data Source window, and then click OK. The Link Tables window opens showing you all of the tables in your remote DB2 data server. For this series, select the EMPLOYEE, INVENTORY, ORG, and STAFF tables, and click OK.

Access 2007 will go through all of the selected tables and link them to the Access 2007 front-end graphical interface. As it passes through all of your selected tables, if it can’t easily find a unique identifier, it will prompt you to assign one such that it can ensure integrity on updates, and so on, to the data set.

For example, in the SAMPLE database, the ORG table doesn’t have any unique identifiers such as a primary key or a unique key, so therefore it will prompt you to select a column (or columns) to define one:

When all of the selected tables have been linked, they will show up in the All Tables view, which by default is located on the left side of the Access 2007 application:

Note: A little cleanup

By default, whenever you create a new database in Access 2007, a default table template called Table 1 is created. Since this won’t be part of our series, you can remove it by performing the following steps:

1.  Select the Table1 tab such that focus is transferred to this table. You will know focus in on this table when the designer part of Access 2007 looks like this:

2.  Right-click the Table1 tab and select Close. Since you haven’t changed or saved anything to the default table template, it will just close and not be created. If you did create it, you would have to right-click the table in the All Tables view and select Delete.

Now you’ve linked Access 2007 as a graphical front end to the DEPT, EMPLOYEE, INVENTORY, ORG, and STAFF DB2 tables. Notice that Access 2007 by default prefixes each table name with the <db2_schema_name>_ qualifier. You may want to override this; after all, the information workers on the other side of Access 2007 don’t care (or don’t know) where the data is stored, they just want to access it (no pun intended). To rename a table, simply select the table, right-click, select the Rename option, and enter a new name for the table. For example, in the following figure you can see that I’ve renamed the PAULZ.DEPT table to DEPARTMENT:

Notice that I didn’t rename the table to DEPT, which is the name in the SAMPLE database. You can see that Access 2007 allows me to offer a level of abstraction over the schema of the database and translate it into business terms. This way, analysts can explore business artifacts instead of the sometimes zany world of naming conventions and limitations.

For this series, I renamed all the tables we’ve just added to Access 2007 such that your All Tables view should look like this:

At this point, you likely want to see the data. It’s easy; just double-click your business artifact:

Wrapping it Up...

In this article, I showed you how to configure Access 2007 as a graphical front-end for DB2 data operations. Specifically, we created a number of linked tables such that no data is actually stored in an Access 2007 data server, yet information workers are free to leverage the rich toolset that it provides for reports, forms, and so on. Additionally, we introduced an abstraction layer from the back-end DB2 data server such that information workers get a business view of the database as opposed to the underlying naming convention, schemas, and so on, used to implement the logical design on the data server.

In the next part of this series, I’m going to show you some of the things you can do now that you have your DB2 data in Access 2007.

» See All Articles by Columnist Paul C. Zikopoulos


IBM, DB2, i5/OS, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States, other countries, or both.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, or service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2008. All rights reserved.


The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

DB2 Archives