Connecting to Microsoft Access

 < Day Day Up > 

Many organizations that depend on Microsoft Access as a data store for departmental and other sub-corporate databases will find InfoPath 2003 a useful alternative to Access forms for many purposes. For some simple scenarios, it's sufficient to create an InfoPath form template similar to the example shown in Chapter 1, "Getting Started with InfoPath." In Chapter 1 you saw how a form template can be created to both query an Access database and submit data to an Access database.

For more information about simple connection to a Microsoft Access database, see "Connecting to an Access Database," p. 10 (Chapter 1).


Using Multiple Tables in a Form

InfoPath 2003 has some functionality to enable form templates to query multiple tables or submit data to them. However, InfoPath has limitations as to how many tables can be involved in a join. Using data from two tables seems reliable, but some users have reported problems when attempting to use three tables.

Using Two Access Tables in a Data Source

In this example, we'll step through the use of two tables in the INFNWIND.mdb sample database. Follow these steps:

  1. Open InfoPath and, if it isn't already open, open the Design a Form task pane using the Ctrl+F1 keyboard shortcut.

  2. Select the New from Data Source option in the Design a New Form section of the Design a Form task pane. The Data Source Setup Wizard window opens.

  3. Select the Database (Microsoft SQL Server or Microsoft Office Access Only) option (see Figure 7.1).

    Figure 7.1. Selecting a database as a prospective data source.

    graphics/07fig01.gif

  4. Click the Next button, and the next window of the Data Source Setup Wizard opens.

  5. Click the Select Database button at the top-right of the window. The Select Data Source window opens (see Figure 7.2). For this example, we will select the file from its location in the filesystem.

    Figure 7.2. The Select Data Source window.

    graphics/07fig02.gif

  6. I have copied the INFNWIND.mdb database to c:\IKS\Ch07\INFNWIND.mdb . Click My Computer and navigate to the relevant directory on your computer.

  7. Click the INFNWIND.mdb file; then click the Open button in the Select Data Source window. The Select Table window opens (see Figure 7.3).

    Figure 7.3. The Select Table window.

    graphics/07fig03.gif

  8. The Select Table window shows the four tables that are part of the INFNWIND.mdb database (it's a stripped-down version of the familiar Northwind.mdb database)Customers, Employees, Order Details, and Orders.

    In this example, we will use information from the Customers and Orders tables. First select the Customers table and click the OK button. Control returns to the Data Source Setup Wizard (see Figure 7.4). (If you select the wrong table, you can click the Remove Table button to empty the Data Source Structure pane. Then add the correct table by clicking the Add Table button, which opens the Add Table or Query window seen in Figure 7.5, select the Customers table, and click Finish.)

    Figure 7.4. The Data Source Setup Wizard showing the columns of the Customers table.

    graphics/07fig04.gif

    Figure 7.5. The Add Table or Query window.

    graphics/07fig05.gif

  9. For this example, we won't use all the columns of the Customers table, so uncheck the check boxes for the Phone and Fax columns.

  10. InfoPath will automatically generate an SQL statement that selects the columns we specified in the Data Source Setup Wizard window. Click the Edit SQL button to open the Edit SQL window (see Figure 7.6).

    Figure 7.6. The Edit SQL window.

    graphics/07fig06.gif

  11. By default, the name used in the InfoPath data source is the same as the name used as the name of the table. However, if the Customers table in our database refers only to old customers, we might want to call the data source OldCustomers . We specify that change in the Edit SQL window by changing the SQL statement from

     select [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode], [Country] from [Customers] as [Customers] 

    to

     select [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode], [Country] from [Customers] as [OldCustomers] 
  12. Click the Test SQL Statement button. If you made a syntax error when editing the SQL, a window like the one shown in Figure 7.7 will be displayed with an option to see more details about the problem.

    Figure 7.7. The window displayed when there is an SQL syntax error.

    graphics/07fig07.gif

    If the SQL syntax of the edited SQL statement is correct, the Microsoft Office InfoPath window shown in Figure 7.8 is displayed.

    Figure 7.8. The window displayed when the SQL syntax is correct.

    graphics/07fig08.gif

  13. After you have successfully tested the edited SQL statement, click the OK button in the Edit SQL window to return to the Data Source Setup Wizard window (see Figure 7.9). The name of the corresponding group in the InfoPath data source has changed to OldCustomers .

    Figure 7.9. Changing the name of a table in the structure of the InfoPath data source.

    graphics/07fig09.gif

  14. We want to access information from both the Customers table and the Orders table, so click the Add Table button. Click the Orders table in the Add Table or Query window and then click the Next button. The Edit Relationship window is displayed (see Figure 7.10).

    Figure 7.10. The Edit Relationship window.

    graphics/07fig10.gif

  15. InfoPath correctly detects that the relationship between the Customers table and the Orders table is defined by the CustomerID column.

    However, we can also manually define a relationship between the tables we choose. Click the Remove Relationship button to remove the automatically created relationship.

  16. To manually create a relationship between the Customers and Orders tables, click the Add Relationship button. The Add Relationship window opens (see Figure 7.11) with the columns from the Customers and Orders tables displayed.

    Figure 7.11. The Add Relationship window.

    graphics/07fig11.gif

  17. Click CustomerID in both the Customers and Orders tables; then click the OK button. The Edit Relationship window opens. Click Finish.

  18. The Data Source Setup Wizard window is again displayed, as shown in Figure 7.12.

    Figure 7.12. The Data Source Setup Wizard after the relationship between the Customers and Orders tables has been defined.

    graphics/07fig12.gif

  19. Click the Edit SQL button to see the automatically generated statement shown in Figure 7.13. Click OK.

    Figure 7.13. The statement to select data from both the Customers and Orders tables.

    graphics/07fig13.gif

  20. Click the Next button in the Data Source Setup Wizard, and the wizard's final window opens (see Figure 7.14).

    Figure 7.14. The final Data Source Setup Wizard window.

    graphics/07fig14.gif

  21. Notice the information in the Summary section of the window: The data source is specified as c:\IKS\Ch07\INFNWIND.mdb , the primary parent table is the Customers table, two tables are included in the data source, and the Submit Status is enabled. When the Submit Status is enabled, we know that the InfoPath form template will be able to submit data back to the database. Accept the default option of Design Query View First, and click the Finish button.

    A query view is automatically created (see Figure 7.15). The Data Source task pane opens automatically.

    Figure 7.15. The automatically created Query view.

    graphics/07fig15.gif

Notice the structure of the data source nested inside the myFields folder queryFields and dataFields . The queryField s are intended to specify a query. The dataField s are intended to specify the result of a query or for adding new data to the database. Figure 7.16 shows the structure inside the queryFields folder expanded.

Figure 7.16. The structure of the data source inside the queryFields group.

graphics/07fig16.gif

Creating a Data Entry View

We chose to have the Query view automatically created for us, so now we have to create the Data Entry view.

QUERY FIELDS AND DATA ENTRY FIELDS

It's important that you are clear about the purpose of the query fields and the data entry fields. If you bind form controls to the wrong fields, you might experience puzzling errors or unexpected results.

The query fields are intended for use in query views to specify what data in the data source is to be queried. The data entry fields are to be used to contain data returned from a query or data intended for submission to the database.


Follow these steps:

  1. From the View menu, select the Manage Views option. The Views task pane opens.

  2. Click the Data Entry view in the Select a View section of the Views task pane. The blank Data Entry view opens in the form area (see Figure 7.17).

    Figure 7.17. The blank Data Entry view just after it has been opened.

    graphics/07fig17.gif

  3. The quickest way to create a data entry view is to move to the Data Source task pane and drag the d:OldCustomers group (remember, we renamed Customers to OldCustomers when creating the data source) onto the form area. A context menu opens (see Figure 7.18) offering three optionsRepeating Table, Repeating Section with Controls, and Repeating Section.

    Figure 7.18. The context menu.

    graphics/07fig18.gif

  4. If we choose the Repeating Section with Controls option, a basic but functional Data Entry view is created, similar in appearance to that shown in Figure 7.19.

    Figure 7.19. The result of the Repeating Section with Controls option.

    graphics/07fig19.gif

A repeating section is created corresponding to the d:OldCustomers group in the data source. Inside that repeating section is a nested repeating section corresponding to the Orders group in the data source.

One customer can have several orders. The repeating section is a suitable form control to reflect that one-to-many relationship.

Click Preview Form in the toolbar. Select Query View from the View menu.

Enter Simons Bistro in the Company Name field on the Query view. Click the Run Query button. InfoPath switches the view to the Data Entry view (which is also used to display data returned from a query). As shown in Figure 7.20, the information for Simons Bistro is displayed along with several repeating sections, each containing information about orders made by Simons Bistro.

Figure 7.20. The result of a query for the customer name Simons Bistro .

graphics/07fig20.gif

Enabling Submit Functionality

The Data Entry view now functions as a repository for the results of a query, but we need to add a Submit button to use the Data Entry view to add new information to the database. Follow these steps:

  1. Open the Controls task pane. Drag a button form control onto the bottom of the form area.

  2. Right-click the newly added button form control and select Button Properties from the context menu. The Button Properties window opens (see Figure 7.21).

    Figure 7.21. The Button Properties window.

    graphics/07fig21.gif

  3. Select the Submit option from the Action drop-down menu on the General tab of the Button Properties window. The Submitting Forms window opens (see Figure 7.22).

    Figure 7.22. The Submitting Forms window.

    graphics/07fig22.gif

  4. Because our purpose is to add a button form control to submit data to the Access database, we select the Enable Submit option (the default). On the Submit drop-down menu, select Submit to a Database (the default). (If you want to prevent users from selecting Submit from the File menu, uncheck the check box labelled Enable the Submit Menu Item on the File Menu.)

  5. We also need to decide what we want to happen after the form has been successfully submitted to the database. Click the Submit Options button to see the available options in the Submit Options window (see Figure 7.23).

    Figure 7.23. The Submit Options window.

    graphics/07fig23.gif

    There are three possible options for what happens after the form has been successfully submitted: the form is closed; a new, blank form is displayed; or the form that has just been submitted is left open.

    Select the Create a New, Blank Form option. Leave the Show Message Indicating Success or Failure check box checked, because it is typically useful to have feedback about whether the form was submitted successfully to the database.

  6. If you want, you can create custom messages for both success and failure in submission. Figure 7.24 shows a slightly tongue-in-cheek pair of messages.

    Figure 7.24. Custom success and failure messages for submission of a form.

    graphics/07fig24.gif

  7. Click the OK button in the Submit Options window and then click the OK button in the Submitting Forms window. Click the OK button in the Button Properties window.

Save the form template by selecting Save As from the File menu. Click the Save button on the Microsoft Office InfoPath window that opens. Save the form template as c:\IKS\Ch07\QueryAndSubmit.xsn , which is also available in the download.

Click the Preview Form button in the toolbar and enter dummy data, as shown in Figure 7.25.

Figure 7.25. Dummy data to be added to the INFNWIND.mdb database.

graphics/07fig25.gif

Click the Submit button to submit the data. Assuming you created the form template correctly, you should receive a message confirming successful submission of the form with the customized message created earlier (see Figure 7.24).

NOT ALL OPTIONS ARE AVAILABLE IN PREVIEW MODE

Not all submission options are available when a form template is tested in Preview mode. For example, if the submission option chosen is to have a new, blank form displayed following submission, the error message shown in Figure 7.26 is displayed.

Figure 7.26. Not all submission options are available in Preview mode.

graphics/07fig26.gif


Successful submission of the data can be confirmed by opening the INFNWIND.mdb database in Microsoft Access and opening the Customers table (see Figure 7.27).

Figure 7.27. Confirming successful submission by inspecting the Customers table of the INFNWIND.mdb database.

graphics/07fig27.jpg

 < Day Day Up > 


Microsoft Office InfoPath 2003 Kick Start
Microsoft Office InfoPath 2003 Kick Start
ISBN: 067232623X
EAN: 2147483647
Year: 2004
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net