InfoPath Dev

First of all, thanks to Hilary, Clay, Matt, Greg and everyone else who has gotten me this far.  You guys are the best.  Here is my scenario:

We use an ERP (JDE E1) to manage our back office which, of course stores all of our data in a SQL database.  I had our DBA create a scaled down version for me that just contained certain elements that I need to include in my forms and he created a stored procedure (or something) to keep my tables updated with any new records in his tables.  I then built an IP form that will query my DB to populate particular fields in the form.  The form also has additional fields that, when the form is submitted, will collect data and write it back to the DB.  I have many users needing access to the form, so it had to be published to SharePoint on our extranet.  This is where it gets ugly:  most of the people accessing the form will get to it from outside the network.  One painful lesson that I learned is that IP tries to make a CLIENT side connection to the database which is just not possible for my situation.  I have gone to great lengths to try to get around this including trying out Qdabra's DBXL, but since my DB is updated by another process, this will not work.  My latest adventure was to create an Access DB with linked tables to my SQL DB and then publish the Access DB to SharePoint.  I got the DB to publish to SP and create all the lists and so forth and it stored a copy of the DB in a Document Library in SP, but when I try to design a form based on that DB, I get an error saying "An unspecified error occurred while attempting to connect to the data source. Infopath only supports SQL and Access databases".  It is clearly an Access DB and nothing that I do will seem to resolve the problem.  I have run across a few other folks out there with the same issue, but no one has been able to come up with a solution for them either.  This project has consumed more time than I could afford and unless I can come up with a solution to overcome this final hurdle, I am afraid that it will have to be scrapped.  If anyone has any suggestions at all, I gladly welcome them.

Hey Fishman.

This is an involved scenario but doable.  Access has issues anyway but the support in Sharepoint is pretty limited.

I am not sure what you ran into with DBXL but there should not be a problem with using the database regardless of the sync from your primary DB.  I would think that would be your best option since the web services will work with external users and you can do everything client side. I would contact Qdabra and go through it with one of their guys and have them validate your setup and show you how it could be done.  I am sure it could be as I have used it in very similar situations.

Hello Clay.  I took your advice and called Qdabra Sales to discuss the DBXL solution.  I talked to one of their sales guys (Ted) and after explaining my situation, he went back to one of his Dev guys to make sure that it would work.  He sent this to me in an email telling me that DBXL will not work:

Our DBA uses a stored procedure or something like that to keep our Oracle information and my database in sync with regards to new records that hit the Oracle database.  My database never talks back to Oracle, I just get any new records 'pushed' up to me.  According to the above information, that is what is preventing DBXL from working.  My outside application needs to access the database in order to insert new records that do not come from the IP form.

Well I think there is some confusion.

I have done a very similar project with Oracle. It is just how you set it up. You would need to layout your data flow specific for this scenario.

With DBXL, they would install their own aditional Qdabra DBXL database and typically you would have some shredded or extracted data. This would contain all of the form data. The data and the forms are linked so the data cannot be disturbed by anything other than the forms or the relationship is broken.

It is just a matter of abstraction.  The Oracle data is source data anyway, since you do not write it back to Oracle.  Keep the new data your solution collects in a new table or table structure that is only edited by InfoPath and you are fine.

I would talk with Ted and work through it again.  Maybe the way it was presented led to this conclusion but definetly with some creativity you can get it to work.  You should be able to architect it so that the Oracle sync does not update the main data from your forms but secondary data.  You can also build your forms to requery the secondary data each time they open so that they get the latest updated data.

The queries have to be run server-side.  Most of my users will be outside of the network and will not have VPN.  When a data source (primary or secondary I must assume) is accessed by the form, IP relys on the client to make the connection and that isn't possible in my situation.  That is what I was hoping that DBXL would do.