How do I write data from an InfoPath 2007 form to a database

Posted on 2009-02-13
Last Modified: 2012-05-06
I have an expense form in InfoPath 2007 that have multiple lines of expenses. How do you write that data to the database.  Example, I'm turning an expense sheet in with three separate lunch meetings, and 2 trips to separate locations, but it's all on ONE expense sheet. How do you write that record to the database?
Question by:CLP-ITT
    LVL 28

    Expert Comment

    Good Question.

    I would recommend Qdabra's Database Accelerator web services suite.  it is the easiest and fastest way to database enable your form and can put you on the path to all sorts of enterprise InfoPath solutions. Lots of cool features and integrations.

    Otherwise, you can do this with standard infopath but it is not real pretty.

    First you would need a parent table for reports and a related child table for the expense line items.
    Then you would need to start over with a blank form and design it originally as a database form.
    You would then get the design and layout back to what you wanted and you could enter an expense and submit it to SQL.

    You would not be able to submit the attachments with the base infopath method, not supported, but you can with Qdabra's web services.

    Qdarba also has an expense report template that you may be interested in to.  I would at least get a demo from them or do some research.


    Author Comment

    Ok, so let's say I don't have a couple a grand to spend. What other ways are there to do this? Are you saying that I can't have the part where I am adding an attachment without using some sort of web service?
    LVL 28

    Expert Comment

    Yah unfortunately large file types (attachments, rich text) are not supported in the basic database connectivity. You also cannot query and submit to more then one set of tables, which is often a problem with enterprise type solutions.

    InfoPath was ultimately designed to leverage web services and you have the most power and flexibility when you use them.  You either have to create them yourself or or purchase them (Qdabra, Sharepoint, Etc).

    So that is the bummer is that you would have to completley redesign and the attachment could not be databased.

    The upside with InfoPath, SQL, and web services is that you can quickly and easily design, modify, maintain forms and you have the power to build about any enterprise solution very rapidly.  

    You can download the DBXL for free to do a proof of concept.


    Author Comment

    You said speaking of web services... "You either have to create them yourself or or purchase them (Qdabra, Sharepoint, Etc).

    We have MOSS 2007 and that is what we will be deploying this to.
    I envision the user going to an area of SharePoint and clicking New Expense Form then this standard form comes up.  It's pulling some data so we can minimize fat fingering certain data.  After they are ready they hit submit and instead of putting the data to a form, we write it to a database.

    We just thought that we needed a separate database to put all this data to so it would be easier to write reports and potentially automate sending this data straight to the General Ledger and cutting a check.  So is there a SharePoint webservice that is already available???
    LVL 28

    Accepted Solution

    You are right. To aggregate data and report on it, and especially with expense data, interface it to accounting so they do not have to open each one and key the data into the accounting system, you need a database.  SharePoint does not provide this ability since they use a flat data structure.  I have MOSS as my distribution point but store the forms via Database Accelerator.  This gives me better form performance and all of the data is available for reporting.

    SharePoint provides plenty of web services, it is built with web services and these are accessible, but since this is outside of SharePoint functionality there is not one for SQL integration.  I go to Microsoft in two weeks for the MVP summit and I will find out what will be in the next version, and I will check on if they are addressing this at all.

    I know right now, multiple groups at Microsoft which use InfoPath forms and SharePoint, also use the Qdabra product to provide the database integration for hundreds of thousands of forms.

    Another issue is that, since SP data stores are flat, that the performance limit on a library is about 2000 items.  So using a database to store your forms gets you out of that limitation as well.  Forms can pile up pretty quickly.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
    SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now