InfoPath Dev

I'm having a problem consuming data from excel. I have several large excel spreadsheets that I want to pull data from to populate fields in an InfoPath form. Currently I have an Access dbase setup with linked tables to the excel sheets (located on my LAN) and then I use an ODBC DSN data connection to get Infopath to talk to Access and retrieve data from excel. This all works ok... I have a form that refreshes the data connection on open, and then a button that has a rule that populates a field on the form based on data retrieved from excel via Access. The strange this is the form only get data two or three times before the data connection breaks. After three times clicking the button that runs the query I get an error: "some rules were not applied" and then when I try to refresh the datasource again I get "the query cannot be run with the following dataobject: [my data connectionname]". When I view details on the error message it tells me "infopath cannot run the specified query" which is kinda self-evident.

If I tell my data connection to autoupdate data on form open it hangs Infopath and I have to kill it with taskmanager. Doesn't seem to be security related as the same problem occurs when all files exist locally, and when the form is set to Full Trust. I'm using Office2007.

Does anyone know why InfoPath can only talk to my Access>Excel datasource a couple of times before dying? I know I could do this using SharePoint lists but would prefer to get it working using data that is in excel.

I don't know much about how to do this, but i'd suggest convert the excel files to lists in sharepoint.  this is probably the easiest as there is a simple custom list option when creating lists.  Then you can use sharepoint web services to call.

 Second depending on how large the excel files are, import into sql server database, or use linq or linked servers and stored procedures to get the information.  Also should use datasets, etc to limit the data pull, to much data at once will definately effect performance.

thanks for the suggestions barmatt80. i solved the problem using an unecessarily complicated work-around. I have a macro in an access dbase that runs saved imports and pulls in the excel files to local access tables. That macro is called by a vba onsave event in each of the excel docs, so when users save the excel docs they automatically update the access dbase. Infopath then talks to the access dbase, and the connection stays active because the access dbase does not have linked tables, but rather its own (imported) tables.

It's a poor solution, but it's working and the excel users are happy. I'm working on moving the data to a sql server dbase (your second suggestion) so I can do away with access altogether.

Uh, sorry for the delay in responding ... we have this tool called Excel to XML ... oh well, too late I guess.