Collect and store data using Access, InfoPath, or both

Applies to
Microsoft Office Access 2003
Microsoft Office InfoPath® 2003 Service Pack 1 or later

Access 2003 provides a powerful set of tools for rapidly building complete database management systems. With its ability to support a rich forms-based user interface and to access and manipulate data from multiple data sources, including Microsoft SQL Server™ 2000, Access is the ideal solution to a wide variety of business automation and reporting needs.

Access is not the only Microsoft Office System product that supports forms-based client development, however. Microsoft Office InfoPath™ 2003 is a new Microsoft Office System program that provides teams and organizations the ability to easily create and work with rich, dynamic forms. Unlike Access, InfoPath is focused primarily on collecting data, not on storing or reporting on that data. By using InfoPath, you can create forms that are easily integrated with an Access or SQL Server database, or, through Web services, any other back end system system that supports XML. In addition, you can use InfoPath to create a form-based solution that saves individual forms on a server or Microsoft Windows® SharePoint™ Services form library.

If your solution requires a database, InfoPath does not replace that database — instead, it provides a flexible front-end that can connect to the database, either directly (in the case of Access and SQL Server), or through Web services. If your solution does not require a database — for example, if you want a solution that allows users to send forms through e-mail to each other, or to save and merge forms by using a SharePoint form library, InfoPath is also a good candidate. In that case, the data in the forms are saved as separate XML files.

By using either Access or InfoPath, you can:

  • Build rich graphical forms to add, edit, delete, or view your data.
  • Connect directly to external data sources.
  • Employ a suite of graphical controls on your forms.
  • Print your forms or your form design.
  • Utilize data validation rules to ensure the correctness of data entered in forms.
  • Export or save form data in an XML format.

Both products let you create a rich and dynamic client-side experience. Because InfoPath supports creating a rich and capable forms-based interface, and because it supports accessing data from SQL Server and from the native database format of Access, you might wonder which product is most appropriate for solving your business automation needs.

In this article we'll provide some general guidance to help you make the right choice.

The key Access difference: Data storage and reporting

It makes good sense to review your needs in light of some key differences between Access and InfoPath. First and foremost, Access is a platform used for creating solutions based on relational database management, while InfoPath uses XML to build forms-based data-entry and editing solutions.

What that means is that Access is mainly concerned with the storage, retrieval and reporting of data. Access stores your data in subject-based tables; the data itself is managed by a database engine that retrieves information and enforces the rules that ensure data integrity. By default, Access uses the Microsoft Jet database engine.

Most forms-based applications require the ability to store the data, once it is entered. In Access, data entered into a form is stored in database tables. Your database should be carefully thought out and designed before you create your forms. This is also true for InfoPath when you use it as a front-end to a database. For solutions that do not require a database, InfoPath will automatically generate the appropriate data source for the controls on your form. For example, if you add a date picker and a text box control within a repeating section on a form (a repeating section enables users to add additional groups of controls while filling out the form), the data source will consist of two fields in a repeating group — one with a Date data type, and the other with a String data type.

Another area to consider is report generation. Many applications require the ability to summarize and print sales data, or group data by region or territory. Indeed, most Access applications utilize the ability to produce printed reports. The report design feature in Access lets you easily create advanced multilevel reports that employ graphics and perform calculations. You might use it to organize and print product sales for a formal presentation, for example. Because InfoPath does not have a reports feature, you will need to use another program, such as Access or Microsoft Office Excel 2003, to generate reports.

If you're doing complex business analysis, you might need to create custom queries that collect information from multiple tables, or perhaps query the database to create a list of all products for a particular supplier. InfoPath lets you query by supplying sample values in a form, but is not really designed for complex custom queries. Access, on the other hand, includes a rich, graphical query design surface that lets you drag and drop connections between tables or use SQL commands to quickly build custom queries.

The key InfoPath difference: Data collection, form design, and XML support

InfoPath is primarily designed for building and filling out forms to collect data. InfoPath does not replace a database in a solution; rather, InfoPath makes it easy to integrate data collection with an existing database, both directly (with Access and SQL Server) and indirectly, through Web services. In addition, you can use InfoPath to build a solution that saves forms to a SharePoint form library or file share. If your data requires a database solution (that is, record-based data requiring one-to-many relationships between the tables), InfoPath can operate as the front-end to such a solution.

For solutions that do not require a database, InfoPath will infer a design and generate an XML data structure for you automatically, based on the form design. If you're thinking this means InfoPath is probably more appropriate for a simpler, forms-automation task, that's likely true. If you need a department-level application where the data is likely to be accessed by many applications, a better choice would likely be to use Access alone or Access with InfoPath.

InfoPath is based on industry-standard Extensible Markup Language (XML), making it ideal for integrating with applications that need to share data in an XML format. When you design a form, InfoPath creates a form template (.xsn) file, which is a cabinet (.cab) file with an .xsn extension. The .xsn file contains standard XML files, such as XML Schema (XSD) and XSL Transformation (XSLT) files. When someone fills out a form in InfoPath, the data in that form is saved or submitted in XML format. This makes it easy for organizations to reuse the data elsewhere, perhaps in an existing process that relies on XML, such as an expense reporting process.

In addition, you can base your forms on XML files that are already being used by your organization. For example, if your organization uses a specific XML Schema (.xsd) file for purchase orders, you can base an InfoPath purchase order form on that .xsd file.

Access advantages

By using Access, you can:

  • Allow more than one type of application to access your database and have the rules that are stored with the database enforced automatically.
  • Create a multi-user department or workgroup-level application.
  • Gather data from multiple data sources for reporting or printing, using one application. For example, you might want to join information from several tables to serve as the source for a report.
  • Add additional tables at a later date.
  • Create complex ad hoc queries. For instance, if you frequently need to join information from multiple tables stored in a database for ad hoc analysis, choose Access.
  • Create printed business reports that may use multiple sections and grouping levels. For example, if you need to list and summarize orders by branch, region and territory, choose Access.
  • Employ an existing library of code written using the Visual Basic for Applications (VBA) programming language.
  • Deploy a runtime version of the database that doesn't require your users to have Access installed.
  • Create a simple Web application to display or edit data in a database on the Intranet. Access lets you create Data Access Pages. A data access page is a Web page that is connected directly to the data in your database. You can use these pages to view, edit, update, delete, filter, group, and sort live data from a database using Microsoft Internet Explorer.

InfoPath advantages

By using InfoPath, you can:

  • Allow your users to fill in a form in offline mode (while not connected) and submit the data later.
  • Create a form-based solution that gathers data and stores each form as a separate XML file.
  • Gather data from multiple data sources for viewing and editing in a form.
  • Save forms directly to a forms library in Windows SharePoint Services.
  • Create forms that users can e-mail to each other.
  • Create a form connected to an Access or SQL server database without resorting to programming.
  • Create a form connected to a Web service without resorting to programming.
  • Automate an existing paper form without having to create a database or resort to programming.
  • Use existing XML schemas to provide the basis for a form.
  • Employ structured data in the form of XML to streamline business processes and workflow or integrate with Microsoft Biztalk® Server 2004.
  • Create a form with one-to-many relationships, to automate an existing manual process or to add to an existing application that doesn't use Access.

Combining InfoPath and Access

The advantages of InfoPath and Access can be combined in a solution that uses InfoPath as the forms-based front-end to an Access database.

Together, InfoPath and Access can provide a solution that stores your data in a relational database and allows users to fill out forms offline, makes it easy to connect to a variety of data sources, consumes Web services, and supports a wide range of workflow scenarios, such as multiple submits and sending forms in e-mail.

You can connect an InfoPath form to an Access database as a primary data source (when you want to edit and add records to the database), or as a secondary data source (for example, when you want to populate the choices in a list box). In either case, making the connection is simple: Using a wizard in InfoPath, you specify the tables and queries in the database that you want your form to work with. InfoPath then automatically creates a form based on the database, including the relationship between the tables, any data validation rules, and the data types of the columns.

Your users can then use the InfoPath form to query records in the database, modify those records, and add new records. Because the form can both submit data to the Access database and save the form as a stand-alone XML file, users can fill in the form while offline, and then submit the data later, when they are online.

For more information about using InfoPath and Access together, see the following articles:

How-to guide: Connecting an InfoPath form to an Access database

Design a form based on a database

Case study: National Clinical Research

Case study: Commercial and Industrial Design Co.

Applies to:
Access 2003, InfoPath 2003