[dba-Tech] XML to access?

MartyConnelly martyconnelly at shaw.ca
Thu Sep 1 18:21:36 CDT 2005


Well ImportXML it works in Access 2003, however in Access 2002, it is 
best described as very wobbly,
where it was first introduced.

If you have an attribute centric xml file of the type generated by 
persisting an ADODB.recordset
you have to use an XSLT transform to reduce it to flat xml before using 
ImportXML.
but if that were the case you might as well bring it back into a table 
via ADODB methods.
However there is a XSLT transform published here that does it.

You cannot import attribute-centric XML (old MS name ADTG) in Access
This is a very specific XML format (MS only), if you are using your own 
Attributes
you may have to write your own XSLT or just parse from the XMLDOM.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q285329

I am still not sure how Access handles an xsd schema, if it is defined 
internally to the xml file
it doesn't seem to accept an external xsd file on import, however it 
does produce one on export.

Code to create an ADTG or ADODB xml file.
Public Sub SaveRecordset()
    Dim rst As New ADODB.Recordset
    With rst
        .ActiveConnection = CurrentProject.Connection
        .CursorLocation = adUseClient
        .Open "SELECT * FROM BusinessDetails"
        .ActiveConnection = Nothing
        .Save "c:\xml\rss\BusinessDetails.xml", adPersistXML
        .Close
    End With
End Sub

Public Sub CountRecords()
    Dim rst As New ADODB.Recordset
    rst.Open "c:\xml\rss\BusinessDetails.xml"
    Debug.Print rst.RecordCount
    rst.Close
End Sub


Jim Moss wrote:

>Jon,
>
>Since nobody else has bitten on this one, you can try using the following:
>
>Application.ImportXML ("c:\somexmlfile.xml"), acStructureAndData
>
>if you only have an xml file. Compare the files to ensure that you have
>all of the data. If data is stored as attributes then you are going to
>need to do a transformation, and then I'm lost as last year's easter eggs.
>
>Does the export from the host generate an xsl ans well as an xml file?
>
>Jim
>
>
>
>  
>
>>Hi all
>>
>>I've not used access much, but as the IT guy here, I've been asked to try
>>and find a way to import data into an access db from an xml sheet, which
>>is
>>seemingly the only way we can oupput from the software we have.
>>
>>So in view of it being the blind leading the blind, as such, is there a
>>SIMPLE way to do it?
>>
>>
>>Jon
>>
>>
>>The information in this e-mail is confidential and may also be legally
>>privileged. The contents are intended for recipient only and are subject
>>to the legal notice available on request from : webmaster at alcontrol.co.uk
>>ALcontrol Laboratories is a trading division of ALcontrol UK Limited.
>>Registered Office: Templeborough House, Mill Close, Rotherham, S60 1BZ.
>>Registered in England and Wales No 4057291
>>_______________________________________________
>>dba-Tech mailing list
>>dba-Tech at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-tech
>>Website: http://www.databaseadvisors.com
>>
>>    
>>
>
>
>_______________________________________________
>dba-Tech mailing list
>dba-Tech at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-tech
>Website: http://www.databaseadvisors.com
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the dba-Tech mailing list