F
Fishman4
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.
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.