Import XML File to a MS Access Table

[ccocker]

hi guys,

I am wanting to import an XML file into MS Access Table - can someone assist in guiding me through the VBA to create the import.

I have some experience in VBA writing Functions,Proce dures etc but XML is new to me.

[ADezii]

Assuming you are using the correct Access Version, this will do the trick for you:

'Import Options:
'acAppendData
'acStructureOnly
'acStructureAndData

Application.ImportXML DataSource:="<PATH to *.xml File>", ImportOptions:=acStructureAndData

[ccocker]

thank you - I did try this and it does work for a basic XML file i.e. I can successfully load a file that I created.

The issue is that the XML file comes from another system and I believe it needs to be parsed. From my searching it appears this can be done using MSXML, however I cannot seem to get it working in Access. I suspect I need a library reference loaded but not sure which one I need.

When I put the following statement into my procedure:

Dim oDoc As MSXML.DOMDocume nt

I get an error saying User Defined Type not defined.

Assuming you are using the correct Access Version, this will do the trick for you:

'Import Options:
'acAppendData
'acStructureOnly
'acStructureAndData

Application.ImportXML DataSource:="<PATH to *.xml File>", ImportOptions:=acStructureAndData

[Hennepin]

This topic is fortuitous for me. I just started to create a module for input and parsing an xml file.
I got the xml loaded into objects in memory now I need to extract data from it. I am going to study xmldom at ‘A Beginner's Guide to the XML DOM’ at http://msdn.microsoft.com/en-us/library/aa468547.aspx to see if this will make it easier.
I believe the library reference to msxml is 'Microsoft XML, v6.0' (this is latest version on windows 7, 64 bit machine).
Or late bind with Set objParser = CreateObject( "Microsoft.XMLD OM" )

[ccocker]

I have solved the problem. I will post my code in a few hours