Microsoft Access

Re: HOW TO query by passing a parameter from Infopath 20007 to a Microsoft Access 2007 database?

Patrick Halstead — Sat, 19 Dec 2009 04:41:42 GMT

There are some new features in 2010 that enable BCS connections to backend data bases and that includes querying those databases. We don't have any explicit "intel" on how to do that with 2010, but you could check Clayton Cobb's site: http://claytoncobb.wordpress.com/?s=BCS - he has various references on BCS but there are some issues and it may not be an exact solution for you.

The best practices is to use a Web Service - that's what SharePoint is and that's what Qdabra provides too for filtering (qWSSP). SharePoint Web Service (WSS 3.0) is free. If you have that, you can filter a SharePoint list using owssvr and some code. For a codeless alternative using owssvr, check out Qdabra's qRules 2.0 - free three month trial will be available next week (or send e-mail to Qdabra). The qRules trial may be the cheapest way for you to accomplish this using SharePoint as your backend database - it's only $199 which is a fraction of the time your team would spend writing a Web Service to do the filtering.

But, Swetha has Access, so that probably is not a solution. For Access, you should check out BCS with ShP 2010. I believe there is closer integration with Access in ShP 2010. In fact, you may be able to use Access to solve the problem (why bother with InfoPath?). Access has a great new integration story for ShP 2010 and InfoPath is better suited for XML forms with SQL and Web Services, not Access.

If you are dead set on using InfoPath, you should consider using SQL instead of Access. Microsoft provides SQL Express for free. Qdabra provides a very inexpensive Web Service (qWSSP) that let's you do parameterized querying. I think it's like $495 or something. Not as cheap as qRules, but still much cheaper than if you had to roll your own.

Sorry I can't be of any more assistance. If you want to hire a consultant to write some client-side code, please send me e-mail.

Re: HOW TO query by passing a parameter from Infopath 20007 to a Microsoft Access 2007 database?

t3itsupport — Fri, 18 Dec 2009 17:50:01 GMT

Patrick,

Can you please provide an example or point to a resource that has further directions on how to proceed? Also, do you know if it is a tad straight-forward in MS Office 2010?

Re: HOW TO query by passing a parameter from Infopath 20007 to a Microsoft Access 2007 database?

Patrick Halstead — Fri, 18 Dec 2009 02:31:56 GMT

Hi Swetha,

It's some work, but you can write code to do this.

Good luck!

HOW TO query by passing a parameter from Infopath 2007 to a Microsoft Access 2007 database

swetha — Fri, 18 Dec 2009 02:27:04 GMT

My scenario :

I have a Microsoft Access 2007 database foobar.accdb with two tables Student(ID|FirstName|LastName) and Scores(ID|ScoreA|SCoreB) which has a primary-foreign key relation on the field 'ID'.

I want to create an Infopath form that can submit new students to the 'Student' table and also be able to retreive information from the 'Scores' table. The Infopath form should be able to take 'ID' field as user input and then query based on it. I created a dataconnection to the database using the wizard. I created a submit connection first to the 'Student' table. Now I want a secondary data connection, which would query the 'SCores' table based on a given 'ID'.

I am not sure how to accomplish this, since parameterized querying is not allowed in the Infopath query wizard(?)

I do not have access to an IIS server and cannot use webservices.