InfoPath Dev

We needed to query into an old database via our infopath forms. Although, this database is old it is used on a regular basis for managing documents and information. This database can be accessed via ODBC . So we used Mircosoft Access. Using MS Access, we are able to access the fields and the data in this old database using ODBC. When you connect MS Access to a database via ODBC you have two options, 1) either you can make a real-time connection (linked table) with the database or 2) you can download the tables. After linking my Access database to the old database via the first option above, I tried to create a connection in an Infopath form to this derived access database. Unfortunately the fields do not show up. But when use the second option of downloading the tables, I am able to see the fields when accessed through Infopath form. Since the infopath forms will be accessing information in the MS Access database derived from the old database. 1) Does this mean we will have to download the tables from the old database regularly, Is there a way to set this download automatically? 2) Does this access database need to be on a server?

Hi TechPundit,

Can you push the Access DB to a SharePoint or SQL server and query from there? If so, that gives you a networked solution which would allow more people to query it dynamically. If you don't care about decentralizing access to the InfoPath form and only use InfoPath for a local laptop solution (for example) you could set up some task to recreate the table at regular intervals. Qdabra Software (sponsor of this free forum) provides an offline dashboard that synchs to an online SQL database. So, you could push your Access DB to a free SQL Express instance on a server and use the DBXL dashboard to pull the data down to a local instance.

Sorry I can't think of other solutions. If I were you, I'd create a DTS job that pushes the Access data to a networked SQL database and pull from that.

Disclaimer: we don't do a lot of stuff with Access but we do partner with Access MVPs so we can find out the answer with some effort if it's worth it....