How to import XML data into MsAccess 2007 Table (without .xsd schema file)

Posted on 2010-01-04
Last Modified: 2013-11-27
Hi Experts,

I have a bunch of xml files that I need to analyze in MsAccess.  I try to import through the MsAccess 2007 Wizard but get the following error:

"Cannot find the specified schema file 'XML-ORDERS.xsd.' Remove or update the file reference, and try again to import."

The header of the aforementioned file has a reference as follows:

"XML-ORDERS xmlns:xsi="" xsi:noNamespaceSchemaLocation="XML-ORDERS.xsd"

What should I do to parse this file and read the bits of information that i need into an Access Table? Do you have any VBA Code to do this?

Thanks in advance!!!
Question by:MasterandCommander

    Author Comment

    I found this site that does exactly what I need in Excel....

    But this piece of code gives the following error (clicking on debug it highlights "Dim rst As ADODB.Recordset") and says:
    "Compile Error: User-defined type not defined"

    Sub Read_XML_Data_1()
        Dim rst As ADODB.Recordset
        Dim stCon As String, stFile As String
        Dim i As Long, j As Long
        Set rst = New ADODB.Recordset
        stFile = "C:\Report.xml"
        stCon = "Provider=MSPersist;"
        With rst
            .CursorLocation = adUseClient
            .Open stFile, stCon, adOpenStatic, adLockReadOnly, adCmdFile
            Set .ActiveConnection = Nothing
        End With
        With ActiveSheet
             'Add the fieldnames to the first row.
            For j = 0 To i - 1
                .Cells(1, j + 1).Value = rst.Fields(j).Name
            Next j
             'Copy the data from the recordset.
            .Range("A2").CopyFromRecordset rst
        End With
         'Closing the recordset.
         'Release object from memory.
        Set rst = Nothing
    End Sub

    Accepted Solution

    Ok.  This does what I want..... I just need to adapt it to my files/database.....


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Title # Comments Views Activity
    Specific format 21 105
    Sql code problem 6 12
    Passing a TempVar to a Combo box form 3 23
    get the maximum value on a condition 3 29
    Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
    HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now