From Sharepoint List to Access DB - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

From Sharepoint List to Access DB

Last post 04-23-2013 05:48 PM by Hilary Stoupa. 35 replies.
Page 1 of 3 (36 items) 1 2 3 Next >
Sort Posts: Previous Next
  • 08-27-2008 07:18 AM

    From Sharepoint List to Access DB

    Hello All!  First of all, I want to thank everyone for all the help that I have received from this forum.  It really is a terriffic resource.  With your help, I have (as a relative noob) been able to develop a solution that allows us to manage our customer care process.  It is a form published to a forms library on SP.  I used the fabulous tutorial by Matt Faus to autopopulate all the information about a particular customer by selecting them from a dropdown - this really made things work much more smoothly - thanks Matt!  The customer list is kept in a list in SP.  I connected another form to the same list that creates a workorder that gets emailed (a technique that I learned here also) to a trade partner to correct issues that our customers have.  Both of these forms libraries are displayed on a SP page so that the administrator can see all the customer care inspections and any open work orders.  It really is pretty slick and has greatly improved our process and made our customers much happier.

    The 'downside' is that it works so well, that the list has grown to such a proportion that the form now takes too long to open as it retrieves the information from the list when it opens.  I have decided to to try to move to Access in order to help with the performance issue and to help ease the process of adding new customers to the list.  I have figured out how to get the list into the DB and build a form with the DB as the main data source and I have all the data and query fields on the form, but I am not sure how this is supposed to work.  When I test the form, I type in the last name of a customer (that is how the admin will search for existing records - by last name, first name) and when I hit 'run query' the form returns the entire DB (about 5k records) rather than just the ones based on the information I entered.  Obvioulsy I am doing something wrong.  Either the DB is designed wrong or form or something.  I really do not know much at all about Access and I am at a loss.  I have read every how-to that I can find and have been digging around here for a good bit to no avail.  Can someone please at least point me in the right direction?  I feel like I am in over my head......

    BPG

    Fishman
  • 08-27-2008 08:08 AM In reply to

    Re: From Sharepoint List to Access DB

    Hey there -- I'm so glad the forum has been of value to you!

    As to your current problem, are the fields that you are entering the query information of last name, first name bound to the appropriate query fields in your data source? When you create a form from a db, you get a group of query fields and group of data fields -- you want to make sure that the information you are querying on is bound to fields from the query group.

    Let me know if your fields are bound correctly and we can start exploring other options, okay?

    Hilary Stoupa

  • 08-27-2008 09:17 AM In reply to

    Re: From Sharepoint List to Access DB

    Hello Hillary!  Thanks for the help - again!  I am just in the learning/testing mode so things are still pretty simple.  I created the database and then open IP and designed a form set to use the db as the main data source.  This automatically created two sets of fields in the data source pane, two buttons (new record and run query) and two areas under each button - 'drag data fields here' and 'drag query fields here'.  I just did what it said to do and dragged each set to its respective area.  I actually did do a moron move and tried to query in the data field and not the query field.  Now the problem has changed and instead of getting everything, it returns nothing.  Do I need to use wildcards in each field that I do not know the information for?   

    Fishman
  • 08-27-2008 09:51 AM In reply to

    Re: From Sharepoint List to Access DB

    No, you shouldn't need to wild card. I'm sure you checked your DB for the last name/ first name combo you are trying, yes? Perhaps try copying directly from the DB and into the query fields to make sure that you should be getting a result (I always start testing at the simplest level....)

     

    Hilary Stoupa

  • 08-27-2008 12:29 PM In reply to

    Re: From Sharepoint List to Access DB

    If I copy/paste, I am good to go, but I need to be able to query on partial entries.  How do I accomplish that?

    Fishman
  • 08-27-2008 12:40 PM In reply to

    Re: From Sharepoint List to Access DB

    When you say partial entries, do you mean enter S in the last name field and get back all the names that start with S?

    For that, I am afraid you are going to need a bit of code -- we'll have to alter the query before pinging the db with it.... What version of InfoPath are you using? Are you familiar or comfortable with VB.NET, C#, jscript or vbscript?

    Hilary Stoupa

  • 08-27-2008 02:18 PM In reply to

    Re: From Sharepoint List to Access DB

    That is exactly what I am wanting to do.  The only programming language that I am comfortable with is Google ++ and I typically employ the cut-and-paste architecture!  I can usually plagiarize and then fudge it to get it to work if I have a bit of guidance.

    Fishman
  • 08-27-2008 03:59 PM In reply to

    Re: From Sharepoint List to Access DB

    Google ++ !!!! That is very funny! Okay, so please tell me if you are using InfoPath 2003 or 2007. They have some differences that I want to take into account, and then I'll see what I can do about settng you up with some code you can modify...

    Hilary Stoupa

  • 08-27-2008 04:02 PM In reply to

    Re: From Sharepoint List to Access DB

    Oh, sorry!  IP and Access 2007.

    Fishman
  • 08-27-2008 04:08 PM In reply to

    Re: From Sharepoint List to Access DB

    Wonderful, IP 2007 means C#, which I actually understand, unlike jscript, where I have to guess a bit. I'll put together a little code sample that you can mod -- it may be tomorrow morning, though, okay?

    Hilary Stoupa

  • 08-27-2008 04:17 PM In reply to

    Re: From Sharepoint List to Access DB

    No sweat.  I am still learning this whole database thing and my setup is all still in test.  I have many more bugs to work out as well as the exact data structure.  I just want to make sure that I have a good understanding of the behavior and functionality before I get too far and realize that I am in over my head!  Thank you so much for your help, Hilary!  I owe you a beer!

    Fishman
  • 08-27-2008 07:20 PM In reply to

    Re: From Sharepoint List to Access DB

    Okay, here we go. First, a little explanation. In your Form Options, under Programming, you need to set your language to C#. Next, you'll need to change your query button. Currently, if you look in the button properties, you'll see it's set to run a query. Change that to Rules and Custom Code. Select the Edit Form Code button. VSTA (Visual Studio Tools for Applications) opens up. You'll see an event handler for your button with a helpful "Write your code here" comment.

    Here is a code sample:

                //get the value of the query field we want to use with a wild card
                XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Customers/@Last_Name", NamespaceManager);
                string querySeed = queryField.Value;
     
                //clear the query field so that it is ignored in the query
                queryField.SetValue("");
     
                //create our custom where clause -- you have some choices here. I've added in the wildcard. You could choose not to, if your users are savvy and can fill it in themselves when searching, all up to you.
                string customQuery = " where ([Last Name] like '" + querySeed + "%')";          
     
                //get the original command (we'll need to set it back)
                AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;
                string origCommand = newQuery.Command.ToString();
     
                //set the query to use the custom command
                newQuery.Command = origCommand + customQuery;
     
                //annnnnnddddd, now we run the query using our shiny new command           
                newQuery.Execute();
     
                //put everything back the way it was.... you're going to need it!
                newQuery.Command = origCommand;
     
                //set the query field back for the user, so they know what they typed in!
                queryField.SetValue(querySeed);

    I've tried to liberally comment the code, hopefully that helps sort it out. If you want to try it as is, you can create a new form off the Northwind sample db for Access -- I used the Customers table, and used "Last Name" as they query field. You can, of course, expand this -- you can even mix these wild cards with filled out fields! So, for example, if you had some drop downs like Department or Area or whatever, your user could select those too. Here is a link to the Microsoft sample I used to figure this out -- mind you, the sample is in jscript, but they aren't so far apart, really.

    Hilary Stoupa

  • 09-02-2008 10:47 AM In reply to

    Re: From Sharepoint List to Access DB

    Hello Hilary! Feel that sharp, stabbing pain right behind your eyes?  Its me again and I am having issues.  I did a copy/paste and changed the name of the query field and I got the following errors:

    Error 1 The type or namespace name 'XPathNavigator' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\dfisher\My Documents\InfoPath Projects\Wildcard Test\FormCode.cs 36 13 Wildcard Test

    Error 2 The name 'MainDataSource' does not exist in the current context C:\Documents and Settings\dfisher\My Documents\InfoPath Projects\Wildcard Test\FormCode.cs 36 41 Wildcard Test

    Error 3 The name 'NamespaceManager' does not exist in the current context C:\Documents and Settings\dfisher\My Documents\InfoPath Projects\Wildcard Test\FormCode.cs 36 147 Wildcard Test

    Error 4 The type or namespace name 'AdoQueryConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\dfisher\My Documents\InfoPath Projects\Wildcard Test\FormCode.cs 46 13 Wildcard Test

    Error 5 The type or namespace name 'AdoQueryConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\dfisher\My Documents\InfoPath Projects\Wildcard Test\FormCode.cs 46 44 Wildcard Test

    Error 6 The name 'MainDataSource' does not exist in the current context C:\Documents and Settings\dfisher\My Documents\InfoPath Projects\Wildcard Test\FormCode.cs 46 63 Wildcard Test

    Are these things that I need to build or something or do I just have it all messed up?

    Thanks for all your help and BTW, I hope you had a great weekend!

     

    Fishman
  • 09-02-2008 12:07 PM In reply to

    Re: From Sharepoint List to Access DB

    Please check under Form Options / Programming and see what you have for your programming language. You'll want C# (not C# IP 2003). Also, did you add an event handler to a button and then click edit form code and place the code in the event handler? 'Cause no, you shouldn't have to build all that stuff... :)

    Hilary Stoupa

  • 11-06-2008 10:31 AM In reply to

    Re: From Sharepoint List to Access DB

    Hello again Hilary!  Sorry to resurrect a blast from the past, but I am stuck again.  I have used this bit a couple of times and it has worked like a charm until now.  I created another form and inserted the code with the necessary modifications to point it to the right place and now I am getting a strange error:

    The query cannot be run for the following DataObject: Main connection
    InfoPath cannot run the specified query.
    [0x80040E14][MSDataShape] Data shape command text contains a syntax error at or near position 2634 in the command. The text near the error is: " "WorkOrders" where ([WWALPH] like '%')".

    What I don't get is that the query field that I am using the code on is not in the table "WorkOrders" that is referenced in the error.  You can see in my (your) code that I am referencing a different table:

    XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Buyers/@WWALPH", NamespaceManager);

    string querySeed = queryField.Value;

    q:Buyers is the parent table and WorkOrders is way down in the tree.  Any assistance you can provide is greatly appreciated - as always!

    Thanks Hilary!

    Fishman
Page 1 of 3 (36 items) 1 2 3 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.