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

Monday Apr 21st 2008 by Paul Zikopoulos

Paul Zikopoulos explores how to configure Access 2007 as a graphical front-end for DB2 data operations.

Without a doubt, Microsoft Access 2007 (Access 2007) is one of the most widely used reporting and data persistence tools. Quite often, I see clients export massive amounts of data from their enterprise systems into Access 2007. They tell me they do this because they have so many applications, or information workers skills in the development of applications, built with an Access front-end.

The main problems associated with this approach are as follows:

1.  There are too many copies of the data. Maintaining multiple copies leads to redundant data costs.

2.  There is too much latency with the data. It takes too long to get the latest data to the people who need it. In addition, everyone sees different data with inconsistent points in time with respect to when the data was captured.

3.  The solution is too complex. This much data movement requires its own life cycle: maintenance, batch jobs, and more.

4.  The data is not secure. Because of its portability, data typically resides on minimally secured laptops.

5.  The solution is too expensive. Numerous components lead to increased costs. These costs are often hidden in distributed organizations, but they can be massive -- core operating system licensing costs, additional data server license costs, network costs, patching costs, security costs for a whole new layer of data access, and more.

What’s more, there often comes a time when the IT staff must try desperately to rein in data sprawl and to solve the problems just mentioned. For example, governance is a great motivator for solving the sprawl of Access databases. Faced with such a daunting task, some IT departments define a mission to move Access databases to an enterprise data server, likely Microsoft SQL Server.

I’m not going to perform a competitive analysis of these data servers in this series; however, I will note that I’ve seen dedicated DB2 shops feel that they didn’t have a choice but to select SQL Server for such a mission because they would incur a productivity hit to their project development cycles. I’m writing this series to give you the confidence to let a data decision be a data decision and a tooling decision be a tooling decision.

Now consider leaving all that data in your DB2 (including a DB2 for z/OS) data server and just accessing it. After all, if your enterprise data warehouse (EDW) is built on DB2, it can scale pretty much limitlessly and is designed to handle all kinds of workloads. Or perhaps take those Access 2007 tables and move them to a DB2 data server, but keep all the application logic, reports, forms, and so on, in Access so as not to lose any productivity for your analyst and application development teams.

If you did this, you could achieve the following benefits:

1.  A dramatic reduction in the number of copies of the data, which can help support the goal of presenting a single view of the business through a single version of the truth.

2.  Reduced latency time with fewer input and access paths and near-real-time load and access tools providing up-to-date business intelligence.

3.  A simplified architecture making the environment easier to manage for faster solution development and quicker return-on-investment (ROI)!

4.  A more secure solution since data is locked in one place with enterprise-wide policies.

5.  A less expensive solution. Fewer components lead to reduced costs in both infrastructure and human resources; all this results in lower total cost of ownership (TCO).

The following figure summarizes these approaches:

In a previous series, I detailed how you can solve the same challenges that occur when Microsoft Excel is used to store (rather than just analyze) data in your enterprise. In this series, I’m going to show you how your database selection has no bearing on the productivity your information workers experience with Access; specifically, I’m going to show you how to hook up Access 2007 (the latest version) to a back-end DB2 for Windows data server because things have changed since Access 2003 and versions before that.

Before you start

This series assumes that you have DB2 for Windows installed on your local machine; furthermore, it assumes the SAMPLE database has been created on your local machine too. If you don’t, you can create it by entering the db2sampl –xml –sql command from your operating system’s command prompt. To get a free copy of DB2 that you can use even in production, check out DB2 Express-C.

You can have a remote connection to the SAMPLE database as well, but you need to ensure that you have the proper authorities to catalog data servers, administer the ODBC settings, access to tables, and so on. Generally, I like readers to follow my articles using a local machine, where they created the SAMPLE database personally and have Administrator authority since they will generally have the proper authorities to complete all the steps in the article.

A comment on Access 2007 file formats

Access 2007 has two ways you can connect to external data servers. First, you can link to the target data server. When connecting to an external data source, you can use a main database (MDB) file or an Access 2007 database (ACCDB) file. The ACCDB file format is new in Access 2007 and takes the place of the MDB extension used in previous versions; I’m commenting on both of them here in case you still have a lot of MDB files in your enterprise.

Alternatively, you can use an Access Data Project (ADP). ADP interfaces are built using OLE documents that only contain the front-end presentation items such as an Access report or a form that you may have created. In the background, these objects explicitly connect to a remote data server. ADPs have been around for a while and they don’t look like they’ve been enhanced much in Access 2007. This may lead one to surmise that they are being replaced by MDB and ACCDB files, but that’s my gut feeling.

In Access 2007, MDB and ACCDB files are more useful formats than ADPs: they can link to multiple data sources, don’t have the row limits that ADPs seem to have, and more. For this reason, I’ll try to stick with these more useful formats and stay away from ADP examples.

Getting your DB2 data into Microsoft Access 2007

To use Access 2007 as a front end to your DB2 data server, you first need to create a project. By default, when you start Access 2007, the Getting Started with Microsoft Office Access launch pad opens. For this example, in the Featuring tab, select Blank Database, enter the name DatabaseJournalAccess2DB2 in the File Name field, and click Create.

To connect Access 2007 to a back-end DB2 for Linux, UNIX, and Windows data server, you need to start with a database connection. You can link up Access 2007 to DB2 using the ODBC Database option available within the External Data tab in the Table Tools ribbon:

When you select the ODBC Database option, the Get External Data – ODBC Database window opens.

You can see in the previous window that Access 2007 gives you the option to import tables and their respective data such that they persist in Access 2007. You also have the option to front-end those tables directly by linking to the data source and creating a linked table. With a linked table, the data is available to Access 2007 users, but the data resides on a remote data server. Earlier in this article, I described why storing the same data in multiple places is not good for business. You can avoid doing this by selecting Link to the data source by creating a linked table and clicking OK.

The Select Data Source window opens, as shown below. This window should look familiar to you if you’ve worked with ODBC data sources before and perhaps even if you haven’t.

Assuming you aren’t working with your database connection via a DSN file, select the Machine Data Source tab, select the target data server from the Data Source Name field (in this case, SAMPLE), and click OK.

I didn’t mention in the “Before you start” section that you must have the SAMPLE database registered as an ODBC source (or at least access to the DSN file that represents a connection to the SAMPLE database). I didn’t mention it because many times this will be done automatically for you when you create the SAMPLE database, and typically, you won’t be working with the SAMPLE database for production environments anyway. You can specify that a DB2 database be added to the Machine Data Source list shown in the previous figure when cataloging a remote DB2 data server.

For example, you can use the Configuration Assistant to add a new data server connection using the Add Database using Wizard option and optimize the DB2 database connection for Access as well as register it as an ODBC data source on your server. Alternatively, you can have DB2 create a DSN file for you to redistribute the connection information or have the connection only appear for a specific user defined on your server:

If the target DB2 data server that you want to connect to doesn’t show up in the Data Source Name list, simply click New to add it. The Create New Data Source wizard opens. Select the appropriate radio button that corresponds to the way you want this target data server to be accessible on your system. For our example, select System Data Source, click Next, select the IBM DB2 ODBC Driver (it is automatically installed when you install DB2 on Windows or a client), click Next, and then Finish. This process is shown below:

You can see in the previous figure the two IBM DB2 ODBC Drivers in the Create New Data Source window. This is because DB2 9 supports multiple-installation copies of the product. For example, in this case, I have one version of DB2 9.5 on my machine (denoted by DB295Main) and another separate installation running at the DB2 9 level. This feature is terrific, especially for migration and maintenance.

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:

Click for larger image

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.

Mobile Site | Full Site