Using IP 2007 , I’ve created an InfoPath form that I’ve saved down to an InfoPath 2003 file type that consists of dozens of fields; the data elements organized into groups, repeating groups; lots of complexity. I now want it to work with an MS Access 2007 database, and have learned that to work with MS Access, the form creation process must start with the data fields already existing in MS Access, and that brought into InfoPath at the beginning of creating the form. I am trying to learn how to accomplish this in the most efficient way; and trying to determine if it is even possible to keep my InfoPath form data schema versus what MS Access database requires as a data/table structure.
I have read the MS Access 2003 article “How-to guide: Connecting an InfoPath from to an Access Database” referenced on other posts here about this subject. The article largely presumes that the data fields already exist in Access database, organized correctly into tables, primary keys defined, etc. As I did not start the form design like this, I am trying to avoid having to manually enter the many dozens of data elements from my InfoPath form as data fields in MS Access. I’m trying to figure out how I can start with the data schema in my existing in my InfoPath form to import into MS Access to create the data fields automatically. Is there a good approach to do accomplish this? (I know I can import the schema into MS Excel, if that is the correct intermediate step to use an MS Excel file to import into MS Access; however, I’m not sure the right way to get all the fields listed correctly in MS Excel.)
Secondarily, when a MS Access database and InfoPath are working together, does the table structure in the MS Access database correspond exactly to the InfoPath form data schema? (i.e., must the MS Access database tables correspond exactly to InfoPath nodes/groups?) If so, this is confusing as I did not design the form as a logical database with tables. I’m wondering if this is an inherent constraint when using an InfoPath form to work with MS Access. So not clear at all to me the relationship that needs to exist between the MS Access database structure (i.e., tables & fields) and my InfoPath data schema structure (i.e. groups, repeating groups, elements, etc.)