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

April 28, 2008

If a sort operation has been placed on a column, Access 2007 will alert you to this using a sorting icon () beside the columns to which a sort condition has been applied. You can work with the sorting technique applied to any column by clicking this icon and selecting the appropriate action:

You can choose to hide columns too. Simply select the column header (), right-click the column, and select the Hide Columns option:

Hidden columns aren’t removed from your table but rather simply hidden. To get them back, just right-click anywhere in the data grid and select Unhide Columns:

Another feature I like when working with large data sets is the ability to quickly search the data set for specific values using the search bar located at the bottom of an Access 2007 data grid. In the following figure, you can see that I entered an employee’s name in the search field, and Access 2007 quickly located the first occurrence of that entry in the data grid:

You can use the row navigator to move focus from row to row using the controls located at the bottom of the data grid (), or just manually enter a row number in the Record field () and Access 2007 will jump to that record in your data set.

Copy and paste operations make it easy to move data around:

I’ll show you in a subsequent article in this series how useful this feature can be when administering a DB2 data server.

Access 2007 has a nifty feature called quick filters, whereby you can select a tuple from the data grid, right-click, and select a filtering option. For example, in the following figure you can see that I applied a quick filter such that only those employees who belong to department 20 are shown in the data set:

If a data set is filtered, Access 2007 uses a filtering icon () in the column to alert you to the fact that a filter has been placed on the column’s data distribution. You can alter the filter, turn it off, change its semantics, and more by clicking the filter and selecting the appropriate option:

Wrapping it Up...

In this article, I showed you how to work with data sets in Access 2007 that contain DB2 data. As you saw, you get all the productivity that you are used to with Access 2007 and all the data management benefits from DB2 that your IT department wants. I also demonstrated in this article some of the various data manipulation language (DML) operations you can perform on your DB2 data from Access 2007, how authorization controls (among other things) that you apply to your tables in DB2 are dynamically extended to the linked tables created in Access 2007, and more.

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, such as create DB2-based reports, forms, and more.

» See All Articles by Columnist Paul C. Zikopoulos

Trademarks

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

Microsoft is a trademark of Microsoft Corporation 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.

Disclaimer

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.