|
Connect InfoPath to SQL Data Microsoft describes as a forms-based application that lets information workers generate and share structured XML data documents ( or InfoSets) that conform to an underlying XML schema document (XSD) file. You can base a data entry form on almost any existing schema that conforms to the W3C's three-part . If you don't have a schema, InfoPath generates one automatically while you're designing the form. Microsoft also calls InfoPath the "premier smart client" for document/literal XML Web services. When I installed InfoPath from the Microsoft Office System 2003 Beta 2 package, I was surprised to find a feature that the early press releases and analyst reviews didn't mention—direct connectivity to Access (Jet) and SQL Server 2000 databases. In this article, I'll give you an overview of InfoPath's data handling and form sharing capabilities with a simple order review and editing form example that uses SQL Server 2000's Northwind or Access 2003's NorthwindCS sample database. The solution demonstrates many InfoPath features, such as local data validation, databound dropdown lists, and form sharing options. InfoPath's Design a New Form task pane has a New from Data Source link that launches the Data Source Setup Wizard. The Wizard lets you choose one of three data source types—XML Schema or XML Data File, Database (Microsoft SQL Server or Microsoft Access Only), or Web Service. The three choices correspond to InfoPath's XMLFileAdapter, ADOAdapter, and WebServiceAdapter objects. Selecting Database and clicking the New Source button starts the Data Connection Wizard to generate an ActiveX Data Objects (ADO) connection string for the form's primary data source. Continuing with the Data Source Setup Wizard lets you add the form's underlying tables or views. I chose the Orders and Order Details tables and established a relationship on the OrderID fields (see Figure 1). Next, I clicked the Modify Table button and specified a single Orders record for the form in the Sort Order dialog. Another couple of clicks generated a new InfoPath form template with Query and Data Entry views (see Figure 2). Separate Query and Data Entry views aren't necessary for a simple data entry form, so I deleted the empty Data Entry view and removed all but the OrderID label and text box from the Query section. Then I dragged the d:Orders data element below the Query section to create a new data entry section with a repeating table for the Order Details records. After rearranging the labels and text boxes, I clicked the Preview Form button, typed 11076 in the OrderID text box, and clicked Run Query. Voilá! A data-bound InfoPath form in less than five minutes (see Figure 3).
|