Is it Possible to submit InfoPath forms to an Access DB on SharePoint?

Posted on 2012-04-12
Last Modified: 2012-04-13
We have InfoPath 2010, Access 2010 and SharePoint 2010 Enterprise

I need to know if it is possible to use InfoPath to submit forms to an access DB on SharePoint.

I don’t need to know how to do this right now, I just need to know if it is possible.
Here are the details of what we would like to do.

We need this for employee leave and benefit tracking. Currently we are using a complicated Excel workbook to do part of this.

What I would like to do is:

have an access database that holds the following:

a. Employee information (contact info, date started,  training info,  job description etc)

b. Leave earned (Overtime, Sick, Vacation)

c. Leave taken (overtime, sick, vacation)

d. Leave that can be advanced (usually vacation you could earn between now and the end of the fiscal year)

e. Available leave (overtime, sick, vacation)

f. Available benefits (health, education)

g. Benefits used (health, education)

h. Generate reports corporate wide on the above

We would like to have InfoPath forms that do the following:

a. When the form is opened from sharepoint, based on ID of logged in user, populate the form with some of the employee information, and the leave/benefits available

b. Have place in the form to apply for leave (date range and leave type)

c. Use a workflow to submit the form to a supervisor

d. Supervisor approval gets forwarded to HR

e. HR approval sends approval or deny to staff and submits the leave (dates and type) to the access DB and applies it to that employees leave.

I also need HR to have the ability to override some of the InfoPath information that is submitted to Access. (ie. If date range for leave includes weekends, holidays or any other office closure those days need to be deducted from the leave taken)

I would like all this to be handled via SharePoint and Ideally, the logged in user would have access to all their own data so they could see all their available leave, leave taken, benefits used etc.

Is this a strategy that will work?
Question by:itkadmin
  • 3
  • 2
LVL 28

Accepted Solution

clayfox earned 500 total points
ID: 37837843
This is all doable and a good solution except for the Access Part.

1. You can host an Access Data Base in SharePoint.  It would have to be hosted on a shared drive everyone would need to have access to.
2. Access is not really designed for this type of enterprise solution. I would migrate what you have to SQL.  SharePoint is running on SQL so you have a server already.

You can certainly host the form in SharePoint and the users would not really know where the data is going.

Typically you would make a form that would look up data out of the database each time the user used it.  So do not save any data in forms and have them open them up again unless there are some workflow processes.

Best to just have a database access form. They open a new one each time and query what they want.  that way the data is consistent every time and info is not locked in some other form no one know about.   You can have an email submit that would send to people to notify of the workflow, but again when they open the form it would query the database first.  SO essentially every action gets submitted to the database and everything is queried for consistency. You may want a status or a field to track the stages of the process and be able to have logic that acts accordingly.

I also often make an admin form for HR which would have more functionality to edit or whatever.  So make one or more forms for the standard process and then one for HR specifically.

Hope that helps

Author Comment

ID: 37842126
Thanks a million for your reply.  Sorry I took a day to get back to you.

I work for a small charitable organization, so we are pretty much on our own to sort this out.

I haven't worked directly with databases at all, just the SharePoint interface for libraries, lists etc. I have some experience with MySQL for websites. I get what you are saying about access.

I'm not even sure what tools to start with as far as building the sql database. Do you know of any reference URLs I could use to get started? I use google, and experts-exchange to figure just about everything out. What software do I need to build the database? Should I do it all in Access then import to SQL? Right now we haven't begun this project. I prefer planning it all out before I get started. Otherwise I can get things working, but a year later I have no idea how or why something was done. Too many little fixes that should have been dealt with properly to begin with and it all becomes a horrible mess.
LVL 28

Expert Comment

ID: 37842178
If you have a SharePoint Server you have SQL and the management suite. It has all you need to create tables and views, etc.

You could do this entirely with lists in SharePoint, but to your point it would not be as good in the future for performance, etc.

I have been mucking with Microsoft SQL for a long time but would not be too different from MySQL and I would start with Microsoft's support of it first.

Author Comment

ID: 37842216
Yeah, I tried something similar in sharepoint lists when we had SP2007. Didn't work well for 2 reasons.
1. some of the formulas we use for calculating leave were more complex than SP was willing to deal with.
2. Too much relational stuff going on. This might be possible in lists, but I couldn't figure it out.

Thank you so much! I suspect I'll be posting a lot once things get rolling.

Author Closing Comment

ID: 37842220
A lot of help, friendly too! Thanks

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JavaScript waiting 14 67
Before insert Image (OLE) resize image 11 42
Library not Registered 16 42
Licensing Sharepoint 2016 for developers 11 26
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Before you can digitally sign infopath forms, you must have a digital certificate. Microsoft Certificate Services will need to be enabled on a Windows Server 2008 to facilitate the creation and verification of the digital certifciates on the web ser…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

930 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

8 Experts available now in Live!

Get 1:1 Help Now