help needed: example of access <-> web service <-> infopath

[dt_watson]

Does anyone have a simple working example using access for the data source
and a web service to INSERT or update that database when using SUBMIT from
Infopath? It looks like a lot of the questions here are between infopath and
a database directly.

Any help would be appreciated and credited.

If you have any files to send, please send them to: david at aktivnett dot no

 

[Scott L. Heim [MSFT]]

Hi,

Here are some steps:

1) Create a sample web service using a process and code similar to the
following:

** NOTE: This is using VB.NET against the Northwind sample database and the
Customers table.

- Add an OLEDB Connection object and set the connection string to your
Access database
- Add an OLEDB Data Adapter and complete the wizard to select the
Customers table
- Right-click on the data adapter and choose "Generate Dataset" and add
this to the designer
- View the code behind the designer and add the following "web methods":

<WebMethod()> _
Public Function ReceiveDataSet(ByVal CustID As String) As DataSet
Dim strBaseCommand As String = oCustDA.SelectCommand.CommandText

If (CustID <> "") Then
oCustDA.SelectCommand.CommandText = strBaseCommand & " WHERE
CustomerID = '" & CustID & "'"
End If

oCustDA.Fill(OCustDS)
oCustDA.SelectCommand.CommandText = strBaseCommand
Return oCustDS
End Function

<WebMethod()> _
Public Sub SubmitDataSet(ByVal ds As DataSet)
oCustDA.Update(ds)
End Sub

- Build the web service and press F5 to run it - this will launch the
default page (Service1.asmx) and you should see the 2 methods from above.
Click the "ReceiveDataSet" method, enter: BERGS in the custID field and
click Invoke - if this works, you should see an XML file with a schema and
the customer BERGS.

2) You will need to add the "ASPNET" user account to the Access database
folder and the Access database file for this to work. That user will need
at least read and modify permissions.

3) Create the InfoPath template
- Choose the option "New from data connection" and choose web service
- Select Receive and Submit data
- Enter the URL to your web service
(i.e.http://localhost/Webservices/AccessWebService/Service1.asmx?WSDL) and
click Next
- Select "ReceiveDataSet" and click Next
- The default name is fine here - click Next
- Click Next again, choose "SubmitDataSet" and click Next
- Click the "builder button" next to Field or Group and drill down through
"dataFields" until you see something like: ns1ataset
- Select this node and click OK
- Click Next and then click Finish
- Drag your "dataFields" to your form and whatever "queryFields" you want
and test.

As an example, enter a customer ID (i.e. BERGS) and execute the query.
Assuming the record is returned, make a modification to the data and click
Submit from the File menu - the changes should have been saved to Access!

I hope this helps!

Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.