We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


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

CLP-ITT asked
Medium Priority
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?
Watch Question

Clay FoxDirector of Business Integration

Good Question.

I would recommend Qdabra's Database Accelerator web services suite. www.qdabra.com  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.


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?
Clay FoxDirector of Business Integration

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.



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???
Director of Business Integration
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.