Use Microsoft Access to interact with your DB2 data
An easy way to work with DB2 data
Many people tend to think of Microsoft Access and IBM DB2 database servers as adversaries. Both are database applications. Of course, DB2 is more suited for enterprise-based, mission critical applications, with its proven track record of scalability and performance. But what very few people realize is that Microsoft Access can live in harmony with DB2. Microsoft Access can be used as an easy-to-use front end for accessing and interacting with DB2 data. This article shows you how to do just that. It demonstrates how to create forms and generate reports using Microsoft Access that interact with an underlying DB2 data store.
What is assumed
For this Microsoft Access/DB2 Universal Database tandem study, it is assumed that you have Microsoft Access installed. It will also be assumed that you have DB2 for Linux, UNIX, and Windows installed. You will be creating a simple DB2 database that Microsoft Access will interact with. For the sake of simplicity, it is assumed that DB2 and Microsoft Access coexist on the same physical machine. If your DB2 instance resides on a different machine, don't worry. This article also covers how to talk to a DB2 instance over the network.
A sample database to play with
To create a sample database, complete the following steps:
- For the purposes of this demonstration, begin by starting up the DB2 command line processor and creating a database to experiment with:
db2 => create database accdb2
and connect to it (I am assuming you have a user of db2admin and password of db2admin):
db2 => connect to accdb2 user db2admin using db2admin
- Create a table called accounts. This fictional table mocks a storage table of account numbers:
db2 => create table accounts(accountnum varchar(64) not null primary key, firstname varchar(30) not null, lastname varchar(30) not null)
The simplicity of the table is intentional for purposes of tutelage.
- Add a few values to the database:
db2=> insert into accounts values ('0001','George','Washington')
db2=> insert into accounts values ('0002','John','Adams')
db2=> insert into accounts values ('0003','Thomas','Jefferson')
Link to DB2
Now, you need to prepare Microsoft Access's ability to talk to your DB2 database. This is achieved through an Open Database Connectivity (ODBC) connection. Complete the following steps:
- From the Microsoft Windows Control Panel, go to Administrative Tools icon and the then choose the Data Sources (ODBC) icon as shown in Figure 1 below:
Figure 1. Data Sources (ODBC) icon
- Choose the Add... button under the "User DSN" tab as shown in Figure 2:
Figure 2. Add a data source
- From the subsequent "Create New Data Source" screen, choose the IBM DB2 ODBC DRIVER, and click Finish. Note that this option only shows up if you installed the DB2 ODBC Driver component when you installed DB2 on the same machine you are running Microsoft Access on.
Figure 3. Data Sources (ODBC) icon
- Select the ACCDB2 from the database alias pull-down menu, and in the Database alias field, provide a name for your data source. I usually tack on DS after the database name. Click OK to proceed.
Figure 4. Data Sources (ODBC) icon
- It is assumed that your database coexists on the same machine as Microsoft Access. If this is not the case, you could have alternately first cataloged your remote database using either the catalog command on the command line processor or using the DB2 Configuration Assistant. After doing so, you should be able to see the remote database (shown as the name you catalogued) in the Database alias pull-down menu shown in Figure 4.
Link Access to your DB2 tables
To link Microsoft Access to your DB2 tables, complete the following steps:
- From Microsoft Access, go to File > New, and select Blank database. Don't worry, you won't be storing our data in Microsoft Access, you just need a Microsoft Access database as a logical container for the forms and reports you'll be creating. Find a location on your hard drive where you want to store your database. In this article it is called accessdb.mdb as shown in Figure 5. After specifying a name, click Create.
Figure 5. Creating a new database in Access
- From the subsequent screen, right click Tables under Objects, and choose the Link Tables... context option.
Figure 6. Link tables
- On the subsequent Link screen, from the Files of Type: pull-down menu, choose the ODBC Databases() option.
Figure 7. Link screen
- On the Select Data Source screen, choose the Machine Data Source tab. In this tab, you should see the ACCDB2DS data source you established earlier. Select this, and click OK.
Figure 8. Select data source
- Provide your credentials to connect to the DB2 database. Specify your DB2 user ID and password, and click OK.
Figure 9. Enter credentials
- If your credentials are accepted, you are presented with a Link Tables window. Here you should be able to see the DB2ADMIN.ACCOUNTS table in the tables screen. Select the table, and click OK. Notice how Microsoft Access discovers all the tables in the database, including those in the SYSCAT schema. Also note that in this window, you can specify multiple tables, but for simplicity, only choose one table.
Figure 10. Link tables
- Double click on the newly created DB2ADMIN_ACCOUNTS icon. In doing so, you should be presented with the data housed in your DB2ADMIN.ACCOUNTS table as shown in Figure 11:
Figure 11. Data in selected table
Note that if you change the data in this screen and close the Table window, you see your changes persisted in your database. It is assumed that your DB2 database access privileges allow you to change data in the database.
Business analysts don't really like the user interface you've seen so far. Rather, they tend to use forms to interact with underlying database data.
Use Microsoft Access forms
This article does not delve too deep into the process of creating forms in Microsoft Access, as this article is not a tutorial on Microsoft Access. But rather, it focuses on Microsoft Access and DB2 table linking. However, here is a demonstration to show you that you can quickly make a form in Microsoft Access that allows you to insert, delete, and update records in your underlying DB2 tables.
- From the Object pane, choose Forms > Create form by using wizard:
Figure 12. Create form with wizard
- In the first screen of the Form Wizard, you'll be allowed to specify which fields you want on your form. If you had imported more than one table from your database, you could grab fields from different tables here. Select the >> button to move all the fields of the ACCOUNTS table to the Selected Fields section. This effectively has all your fields included on our form. Click Next.
Figure 13. Form wizard screen 1
- Microsoft Access allows you to choose from a variety of form layouts that control the placement of fields on your form. For the purposes of this article, choose the Columnar format, and click Next.
Figure 14. Form layout
- Pick a style for your form. In doing so, pick the background for your form. Pick a style of your choice, and click Next.
Figure 15. Form style
- In the final Form Wizard screen, you can specify a title for your form. By default, the form is given the name <SCHEMANAME_TABLENAME>. Click Finish.
Figure 16. Data Sources (ODBC) icon
- At this point, you should see your form created.
Figure 17. Access form
Note that when modifying and adding entries using Microsoft Access, the constraints of your underlying database tables are adhered to. You can see this by trying to add two accounts with the same ACCOUNTNUM value. If you recall, your DB2 table was created specifying the primary key of the ACCOUNT_NUM column.
In the form, you can modify existing entries of your DB2 table by simply modifying values in the form. You can also add and delete entries in your DB2 table. To add a record to your database, choose the > button and move to the last record (which should be a blank record). Add an entry as shown below:
Figure 18. Adding a record
- After adding a record in the Microsoft Access form and pressing Enter in the last field, you instantly see your record added to the underlying DB2 table. This can be verified with a simple query on your ACCOUNTS table:
Figure 19. Verify adding a record
Use Microsoft Access reports
Microsoft Access reports represent a quick way that many business analysts use to organize and present data. Reports enable you to format backend data in attractive, informative layouts that can quickly and easily be produced.
Since you have already linked your DB2 tables, you can use Microsoft Access reports to present data housed in DB2.
To demonstrate creating a report from your DB2 data, complete the following steps:
- From the Object pane, choose Reports > Create report by using wizard.
Figure 20. Create report with wizard
- Similar to the Form Wizard's first screen seen earlier, the first screen of the Report Wizard allows you to specify which fields you want on your Report. If you had imported more than one table from your database, you could grab fields from different tables here. Select the >> button to move all the fields of the ACCOUNTS table to the Selected Fields section. This effectively has all your fields included on your Report. Click Next.
Figure 21. Report wizard
- You can specify grouping levels to control the order in which your report data is presented. This technique might be used if, for example, you wanted to break down an employee phone directory by department so that members of each department are grouped together. For the purposes of this article, skip this option. Click Next.
Figure 22. Specifying grouping levels
- You can specify a sort order for your records. For example, you could list your records in your report alphabetically. For the purposes of this article, skip this option. Click Next.
Figure 23. Sorting records
- Specify a layout for the presentation of your report. You have such options as controlling the orientation of your report (that is, portrait or landscape). For our this demonstration, specify Tabular for layout and Portrait for orientation.
Figure 24. Laying out your report
- Following the layout specification of your report, you can specify a style for your report. Pick a style of your choice, and click Next.
Figure 25. Report style
- Specify a title for your report. By default, the report is given the name of <SCHEMANAME>_<TABLENAME>. Specify a name of your choice, and click Finish.
Figure 26. Specify a title
- At this point, you should be presented with a preview of how your report looks. This report can be saved or printed out. Note changes to your underlying DB2 table data (performed in Microsoft Access, through the DB2 command line processor, or any other application accessing the DB2 table) dynamically changes your Microsoft Access report.
Figure 27. Finished report
This article takes into account that many people have Microsoft Access running on their desktop computers installed along with Microsoft Office. It also takes into account that many business analysts are familiar with working with Microsoft Access, but are intimidated by the underlying complexity of a DB2 command line processor. Rather than trying to teach business analysts the intricacies of SQL, this article demonstrated how you can leverage Microsoft Access to act as a front end to your DB2 data.
- Download a free trial version of DB2 Enterprise for Linux, UNIX, and Windows.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Visit the developerWorks DB2 for Linux, UNIX, and Windows zone: Find hundreds of articles and tutorials on DB2 to expand your skills.
- Read more articles by Kulvir Bhogal.
- Now you can use DB2 for free. Download DB2 Express-C , a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.