When Office 2003 is released about a month from now, some of
the versions will contain two new applications OneNote and InfoPath.
Andy Novick shows how InfoPath can be used as a front end to a SQL Server
When Office 2003 is released about a month from now, some of
the versions will contain two new applications OneNote and InfoPath. OneNote
is a note taking application that may be interesting to those of you with
tablet PCs. InfoPath is an XML based forms creation and entry application that
looks very exciting to those of us who create applications that work with a lot
of data on tablets and desktops.
InfoPath is one of several new applications on the cusp of
delivery that enable the display and entry of data in a WYSIWYG forms based
application without the need to write code. I am talking about:
- XForms by the W3C
- InfoPath by Microsoft
- Acrobat 6 by Adobe
Applications that make it easy to create data entry forms aren't
new. For example, Access forms and Access Data Access Pages are both pretty
easy. Adobe has long had forms data entry capabilities in Acrobat. The
acquisition of Canadian company Accelio,
formerly Jetforms, has enabled Adobe to add an XML based infrastructure
to Acrobat 6.0.
What is different today is that the new forms based
applications are enabling the creation of XML data by the application instead
of in a proprietary format. They are also facilitating distribution of the
forms over the web and entry of data both online and offline. That is, once created,
the XML can either be saved on disk for later use or be sent to one of many
possible locations for consumption.
XForms is a W3C standard for Web Forms to supersede HTML
based forms on multiple platforms. The multiple platform aspect is key to the
design of XForms and creators have attempted to abstract the form as much as
possible from the implementation. The standard has reached the "Proposed
Recommendation" stage of development. You can read more about it at: http://www.w3.org/MarkUp/Forms/
What has made the surge in XML based forms possible is the
evolution of various XML standards. Among them are:
XSLT: XML Style Sheets
XPath query specifications
XSD: XML Schema
XML Digital Signatures
Webservices: SOAP, WSDL, UDDI
These standards each make it possible to specify one aspect
or another of an application in XML rather than in code. The ultimate effect
is to create the data in an XML format instead of a proprietary format so that
it can easily be manipulated and transferred from application to application.
InfoPath uses the XML based standards and melds them with Office technology.
To get InfoPath you'll either have to purchase it separately
or have a volume license agreement for the "Professional Enterprise"
edition. However, I have heard a rumor that for a limited time it may be
included in the "Professional Standard" edition. Keep checking
Microsoft's site for more details as the October 21st ship date
One of InfoPath's
strengths is flexibility in the source of data that it can read and update.
The choices include XML files, Web Services and databases. That is, as long as
the database is Access or SQL Server. I suspect the list of supported
databases will grow in future releases.
InfoPath works well with SQL Server, I am hoping that it will be an easy way
to create a robust GUI interface for SQL Server tables. That might save hours
of writing application code. It is also a possible way to display SQL Server
reports without having to have a report writer. Moreover, the resulting forms
share many of Office's features such as spell checking!
article shows how InfoPath can be used as a front end to a SQL Server
database. I have kept the example deliberately simple: we will design an InfoPath
form and enable it to edit the Regions table in the Northwind Traders sample
database in SQL Server 2000. The table is about as simple as they get with
just two columns: RegionID and RegionDescription. The information in this
article is based on the Office 2003 Beta 2 version of InfoPath so there may be
some changes to the product when it is officially shipped.
Designing the Form
step in using InfoPath is to design the form using one of the form creation
wizards. They are started from the Taskpad, which by default is docked on the
right side of the screen. Figure 1 shows how InfoPath appears as I'm about to
design a form with the "New from Data Source" wizard.
The Data source can come from one of three choices as shown
in Figure 2.
Figure 2 Selecting the type of Data Source
I selected Database and the Wizard proceeded to the Select a
Database dialog (not shown). This let's you choose from among the Data Sources
defined on your system or you can create a new data source from scratch. Data
Sources are stored in Office Data Connection files with an ODC extension. These
are HTML files with embedded XML that stores the connection information. For a
SQL Server database the connection information boils down to an OLE DB (ADO)
connection string. Listing 1 shows the text of the
(local) Northwind.ODC file.
Once you have selected the database connection, InfoPath
opens it and allows you to select from the views and tables in the database.
Figure 3 shows the Select Table dialog as I pick the Region table.
Figure 3 Selecting a Primary Table for the Form
Each form has a primary table. The form can show additional
tables linked by their parent/child relationships. Since this is my first attempt,
I have decided to keep things simple and just display Region.
The next step is to select the columns that you want to work
with from the tables selected. Figure 4 shows the dialog as I picked just the
RegionID and RegionDescription columns.
Figure 4 Selecting Columns from the Database Tables
Once you are done selecting the columns, the next step in
the Wizard completes the form definition and begins editing. Figure 5 shows
the final dialog. It allows you to choose the view that is open for editing
when the application opens. Because I do not need the data entry view, I
allow editing to start in the query view.
Figure 5 Completing the Wizard
This dialog turns out to be incredibly important for the
error message that it can show. Look at Figure 6. This is the same dialog as
in Figure 5 but in an earlier trial run. The message in the small type within
the red box tells you why InfoPath will not allow the submission to database
action on a button. The red box does not appear on the screen, I added to so
that you could find the message easily. In fact, I missed this error message
the first few times that I tried to create the form. It was an incredibly
frustrating experience. Two sources of information that I found useful while
trying to resolve this problem were the microsoft.public.infopath newsgroup and
the web site http://www.infopathfaq.com.
Figure 6 Selecting a View for Initial Editing
There are many reasons that InfoPath will not be able to
update the database directly. When it cannot you are not totally out of luck.
You can write your own handler for the submit button in Jscript or VBScript.
It is not actually that difficult but since I am looking for a simple to create
application, it is more than I want to do right now.
Once the form designer is displayed, the fun begins. Figure
7 shows the InfoPath Form designer after the Wizard has created the form. At
the top of the form there is information for the novice InfoPath user about the
presence of two views. I like to see instructions on the form where it is
impossible to miss. However, this is not for the end user of the form so I
have started by deleting the section and adding instructions of my own.
Figure 7 InfoPath Forms Designer After the Wizard is Done.
In addition to changing the instructions, I took the
Deleted the Data Entry view, making the Query view the default.
Dragged the dataFields for Region into a new "Repeating
Section with Controls" section on the form.
Added a Submit Button to submit to the database.
Removed the RegionDescription from the query.
After a little reformatting the form is complete and I can
preview it as seen in Figure 8 after the Run Query button was pressed.
Figure 8 A Preview Window
Here, all the records have been retrieved. However, you can
also run the query to retrieve a particular record or use the New Record button
to add one. Once you are done making changes, press the Submit button and all
the modified records are updated. In this case, I have added an Asia region
and modified the descriptions of the other Regions so that they are more
international. You can see the results in Figure 9. This is after I submitted
the changes and reran the query.
InfoPath will only write a very simple query using the equal
operator. If you would like to have a more complex query, for example using
the LIKE operator, you will have to handle the proper events and construct your
own select string. This is only one of many opportunities that you have to
intercede as InfoPath processes the form.
Sharing the Form
InfoPath forms can be distributed on file shares, web
sites, or even via e-mail. However, to use them with a database the recipient
must have access to the database and a correct Office Database Connection (ODC)
file. The easiest way to achieve this is to publish the form on a file share
on your network and let users get to it from there.
The published file is has a type of XSN. It is actually a
CAB file with multiple compressed files inside. Most of them are in one type
of XML format or another. If you would like to see them, open the form in the
designer and use the menu command File/Extract Form Files.
One of the interesting aspects of InfoPath is that the user
can save the form to a file. Come back to it later and submit it then. This
feature allows a limited type of offline work.
This article has shown how to connect an InfoPath form to
SQL Server and use it to create a simple GUI update form for a simple table.
Seeing how this can be done in just a few minutes leaves me very hopeful about
what can be done with InfoPath. I am looking forward to the full product
release and updated help files.
I am not so sure that the direct database connection is
going to be usable for anything but a pretty simple application. For applications
that are more complex a WebService may be a better way to go. That is the next
avenue that I will investigate for connecting InfoPath to SQL Server data.
See All Articles by Columnist Andrew Novick