Access forms and DB2 Data Server - a perfect match

Tuesday Jan 13th 2009 by Paul Zikopoulos
Share:

Paul Zikopoulos continues his series on DB2 9 and Microsoft Access 2007, explaining in this installment how to build forms that run against DB2 data servers.

In the first part of this series, I showed you how to use Microsoft Access 2007 as a graphical front end to a back-end IBM 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 Part 2, I took the concept of linked tables a step further and showed you how to leverage Access 2007 and DB2 as an integration layer; in addition, I demonstrated some of the business rules that Access 2007 maintains when presenting DB2 data to information workers. In Part 3, you learned how to take the data stored in your DB2 data server and use Access 2007 to create reports. In this article, I’m going to show you how to build forms that run against DB2 data servers, further demonstrating how transparent a back-end DB2 data server is to an Access 2007 developer. Forms are the basis for most applications built with Access 2007 and allow for data retrieval and manipulation (assuming of course that the user has the privileges and rights to view and edit the data).

Before you start...

Make sure that you have linked the DatabaseJournalAccess2DB2 Access 2007 database that we created in Part 1 to the ORG, EMPLOYEE, STAFF, DEPT, and INVENTORY tables in the DB2 SAMPLE database. In addition, I assume you created an abstraction layer over those tables such that the All Tables view looks similar to the following figure. (Notice that the reports that were created in Part 3 are shown under the respective table from which they were built.)

an abstraction layer over those tables

The Form view: creating a simple form in Access 2007

If you want to create a basic form, we’re not talking about anything fancy here, perform the following steps:

1.  From the All Tables pane, select the table for which you want to create a form. For this example, select Staff.

2.  Select the Create tab and click the Form icon within the Forms section of the Office ribbon:

Select the Create tab and click the Form icon within the Forms section of the Office ribbon

Access 2007 automatically creates a basic form that is bound to each column in the table:

Access 2007 automatically creates a basic form that is bound to each column in the table

Now you can work with your form. The remainder of this part of the article will detail most of the actions you can perform within a generated form.

Access 2007 also automatically builds a navigation pane at the bottom of the form. You can use this pane to navigate all the data records in the form. For example, using the Record control at the bottom of this form ( the Record control), you can traverse the data set. You can either directly enter the record number in the numerically bound text box (directly enter the record number in the numerically bound text box) or navigate forwards and backwards (navigate forwards and backwards). For example, click Next Record (click Next Record to move consecutively through the records) to move consecutively through the records in the STAFF table, starting with record 2:

click Next Record to move consecutively through the records

When you enter data in the Search field, Access 2007 navigates to the next record that contains the search keyword, no matter what column it appears in. For example, navigate back to the first record (navigate back to the first record) such that the form shows the Sanders record shown earlier in this article. Now enter the name Davis in the Search field to move to that record:

Now enter the name Davis in the Search field to move to that record

The STAFF table (whose alias is Staff in the All Tables view and in the forms generated by Access 2007) has a total of 36 rows. If you try to navigate to the next row (navigate to the next row) when you’re at the last record, Access 2007 will add a new row that will get persisted to the back-end database (in this case, the STAFF table in the SAMPLE database).

You can also use the New (blank) record button (the New (blank) record button) in the navigation bar to add a new record no matter where you are in the data set. For example, navigate to record number 18 (ID will equal 180 and the Name field Abrahams).

When a blank form is displayed, enter details for the new record; for example:

enter details for the new record

As you enter a new data record, you’ll see an icon that indicates that the record hasn’t yet been saved (an icon that indicates that the record hasn't yet been saved); this icon is located in the top left corner of the form.

To save the new record to the underlying table, press Ctrl+S. You will see the edit icon (the edit icon) change to a row pointer icon (row pointer icon); you will also see that the number of records in the data set is 37 records instead of 36: the number of records in the data set.

If you were to reopen the Staff table or access the table directly from the command line processor (CLP), you would see that the new record has been added to the data set. The following figure shows an example of directly accessing the underlying STAFF table using the DB2 CLP after making a change to the data using an Access 2007 form:

command line processor

Of course, if you aren’t authorized to add data to a table, Access 2007 will return an error indicating that you don’t have the appropriate privilege to add a record to the table:

error indicating that you don't have the appropriate privilege to add a record to the table

Access 2007 forms also offer powerful filtering capabilities. Look at the navigation pane in all of the examples so far in this article; notice that the No Filter field is disabled (the No Filter field is disabled). To filter a form on a column, simply select the column, right-click, and select a predefined filter (or define your own using the action menu bar). Notice that the number of records in the result set is now filtered (the number of records in the result set is now filtered ); you can verify this simply by looking at how many records the form now contains (how many records the form now contains):

how many records the form now contains

To turn a filter off, just click the Filtered button, which changes to say “Unfiltered”; the button toggles between applying and not applying the defined filter to the result set. If you want to redefine the filter, right-click the column and specify a different filter.

The Design view: more complex, but more powerful...

In the previous section, I showed you how to create a simple and quick form. In case you need something more sophisticated, the Design view gives you more programmatic control over everything in your form including sizing, binding, and image layout.

Of course you can create a form from scratch using the form designer (select Create>Form Design from the Office ribbon), but let’s take the form we already have and work with it in the Design view. I recommend this approach to form building: start simple and hand-tune the form with added complexity until you get the form you want.

To change to the Design view, right-click in your form’s white space and select Design View:

Click for larger image
Design View

As you can see in the previous figure, the Design view gives you a lot of manual control over your form.

For example, in the Form view you can’t change the default size of the column labels (or their names, for that matter). You might have tables in your database schema with some pretty odd names. (Believe me, I’ve seem them all, especially with packaged applications.) I showed you earlier in this series how to create object abstractions with customized table names, but you may need to address column names as well. For example, if the form you are building is for data entry in a country where English isn’t the native language, you may want to avoid short forms such as DEPT for DEPARTMENT or COMM for COMMISSION. Using the Design view, you can override the default names. For example, click the DEPT label and change the text to DEPARTMENT as follows:

Using the Design view, you can override the default names

Note: The formatting and techniques discussed in this section also apply to other text or numeric objects, such as the actual data columns on the form. For simplicity, I’ll just focus on formatting labels.

The nice thing about customizing the label is that Access automatically resizes all the labels aligned with the changed label such that they are big enough to accommodate the largest label. What’s more, Access automatically keeps the binding of the field beside the label to the underlying data source, so you don’t need to do anything more than make the form look the way you want it to look. Now click the COMM label and change the text to COMMISSION so that labels in your form now look like the figure below.

Access automatically resizes all the labels

Access also gives you control over the labels (and other objects) and how they will appear. For example, to apply styling preferences to your labels, simply select the labels you want to change (use the Shift to perform multiple selects), right-click, and select a formatting option:

 

apply styling preferences to your labels

In this example, I chose to shadow the labels. As you can see, you can adjust font and fill colors, and more. The best way to learn about these options is to experiment with them yourself; this article is just designed as an introduction into the options you have when working with DB2 data in Access.

the View drop-down list

Tip: You can use the View drop-down list in the Office ribbon to quickly switch between the Form view and the Design view to get a more “end-user look” at your form as you customize it.

 

After experimenting a bit with my form in the Form view, I made it look like this:

sample form

You may be wondering how I was able to get all of these widgets and controls on my form. It’s outside the scope of this article to delve into the details of formatting in Access; rather, I’m just trying to give you some areas you can explore with our DB2 data. So I’ll just say that in the Design view, you can use the Design tab in the Microsoft Office ribbon to add various controls and widgets to your form.

use the Design tab in the Microsoft Office ribbon to add various controls and widgets to your form

The following figure shows some of the different widgets I used to create my form; note that the Properties Sheet can be used to specify properties for each of the widgets on the form:

Click for larger image
that the Properties Sheet can be used to specify properties for each of the widgets on the form

The Layout view: getting it just right

The final view that you should know about when working with Access forms bound to DB2 data is the Layout view. You use this view to work with non-programmatic finer details of the form, such as adjusting font sizes and colors, applying pre-defined stylesheet templates to our form and more. Much like the Design view, the Microsoft Office ribbon has a number of controls that relate to the layout and display characteristics mentioned earlier:

Click for larger image
Layout View

As you can see, you have a number of formatting options. The AutoFormat section of this ribbon is especially powerful; you can take your forms from the drab to the fabulous with the click of a button:

The AutoFormat section

You can also use this view to apply conditional formatting to your DB2 data on the client side:

Click for larger image
apply conditional formatting to your DB2 data on the client side

Wrapping it up

In this article, I showed you how to create a form in Microsoft Access 2007 and bind it to data stored in a DB2 data server. In addition, I showed you how to place programmatic controls on your form and format it for a sharp visual affect, and more. In my next article, I’ll describe related features such as split forms, multiple item forms, a pivot table form, a data sheet form, and the form wizard to make the generation of forms even easier.

» See All Articles by Columnist Paul C. Zikopoulos

Trademarks

IBM and DB2 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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved