[Access 2007] Import multiple XML records into Access, and general file management

[krhazler]

I am a long-time Access user, and have developed plenty of my own databases from scratch, but am an absolute newbie at full-fledged VBA programming. I am hoping someone will take pity on me and lend some code here to save me hours of figuring out stuff you already know.

I need to import XML data generated by a customized Android data collection app (Open Data Kit - ODK Collect, if anyone cares).

Each data record is stored in a file called submission.xml. Each submission.xml file is nested within a uniquely named folder. So for example, I might have a master directory C:/instances. Within that directory are subfolders named Folder1, Folder2, Folder3, etc. (except that in reality they are named with longish strings of random numbers). Within each Folder# there is a file named submission.xml, plus any associated JPG photo files (zero to many).

First, I need to have a FileDialog object on a form, where the user can choose the master directory. I think I can figure that part out from a source I found online.

Once the master directory is specified, the code should loop through the subfolders within that directory, parse the data in each submission.xml into parent and child records, and append the records to existing tables in my database.

Ideally I'd also like the code to move all the JPG files to a "media" folder, and all processed subfolders to a "processed" folder, so that these items will not be processed again next time the code is run.

An example submission.xml file is attached for reference. Added wrinkle: I need to parse out the PT_LOC field into 4 fields, since it actually contains latitude, longitude, altitude, and accuracy.

If anyone has a quick solution I would be so grateful! If someone could even point me to a really good reference source to get me started, that would also be helpful.

Thanks.