[AccessD] Read External File for Path String

Joe Hecht jmhecht at earthlink.net
Fri Jan 20 18:07:23 CST 2006


I have so much to learn I have just ruled out xml for now.
My projects are too small to take advantage of it.

Thanks for the info though.

Joe Hecht
jmhecht at earthlink.net
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
MartyConnelly
Sent: Friday, January 20, 2006 12:55 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Read External File for Path String

If you don't have that high a version of Access here is
method of using 
xmldom to read xml file.
rather than ImportXML method.

'?LoadXMLFile("C:\XML\AdviserDetails.xml")
Public Function LoadXMLFile(ByRef AdviserXML As String)

    'On Error GoTo ErrorHandler
    'needs reference set to XML 4.0 and maybe ADO 2.8
    Dim oDOMDocument As MSXML2.DOMDocument40
    Dim oNodeList As IXMLDOMNodeList
    Dim oAdviserDetailsNode As IXMLDOMNode
    Dim oLowestLevelNode As IXMLDOMElement
    Dim objXMLDOMNamedNodeMap As IXMLDOMNamedNodeMap
     Dim xPError As IXMLDOMParseError
    Dim Mydb As Database
    Dim myrs As ADODB.Recordset
    Dim sTempValue As String
    Dim lrec As Long
    Dim lnorec As Long
    Set oDOMDocument = New MSXML2.DOMDocument40

    oDOMDocument.async = False
    oDOMDocument.validateOnParse = True 'you may want to
parse for errors
    oDOMDocument.resolveExternals = False
    oDOMDocument.preserveWhiteSpace = True
 
    'use if xml disk file
      If Not oDOMDocument.Load(AdviserXML) Then
        MsgBox ("XML File error")
             Set xPError = oDOMDocument.parseError
        DOMParseError xPError
      
       End If
    Set oAdviserDetailsNode = oDOMDocument.documentElement
     Debug.Print oDOMDocument.xml
    
  
    'Set Mydb = CurrentDb
    'Set myrs = Mydb.OpenRecordset("NewTable")
 

   'use appropriate XPath expression to select nodes
  
   Set oNodeList =
oAdviserDetailsNode.selectNodes("//BusinessDetails/*")
 
     lnorec = 0
     lrec = 0
     Debug.Print oNodeList.length
     '   myrs.AddNew
    For Each oLowestLevelNode In oNodeList
        sTempValue = oLowestLevelNode.Text
         lrec = lrec + 1
        Select Case oLowestLevelNode.nodeName
            Case "BusinessName"
                   Debug.Print "Business " & sTempValue
                'myrs!BusinessName = sTempValue
            Case "AddressLine1"
                'myrs!AddressLine1 = sTempValue
            Case "AddressLine2"
                'myrs!AddressLine2 = sTempValue
            Case "Suburb"
                'myrs!Suburb = sTempValue
            Case "State"
                'myrs!State = sTempValue
                Debug.Print sTempValue
            Case "Postcode"
                'myrs!Postcode = sTempValue
            Case "PhoneNumber"
             Debug.Print "Phone " & sTempValue
                'myrs!PhoneNumber = sTempValue
            Case "Email"
                'myrs!Email = sTempValue
            Case "FaxNumber"
                'myrs!FaxNumber = sTempValue
        End Select
        If lrec = 9 Then '9 elements in business details
       ' myrs.Update
        lnorec = lnorec + 1
        lrec = 0
       ' myrs.AddNew
        End If
      Next
     
     MsgBox "Records Added=" & lnorec
     ' Set myrs = Nothing
    'Set Mydb = Nothing
    Set oDOMDocument = Nothing
    Set oAdviserDetailsNode = Nothing
    Set objXMLDOMNamedNodeMap = Nothing
    Exit Function

ErrorHandler:

  '  Call NewError.Raise(Err.Number, Err.Source,
Err.Description)

End Function
Sub DOMParseError(xPE As IXMLDOMParseError)

    ' The document failed to load.
    Dim strErrText As String
    ' Obtain the ParseError object
    With xPE
    strErrText = "Your XML Document failed to load" & _
        "due the following error." & vbCrLf & _
        "Error #: " & .errorCode & ": " & xPE.reason & _
        "Line #: " & .Line & vbCrLf & _
        "Line Position: " & .linepos & vbCrLf & _
        "Position In File: " & .filepos & vbCrLf & _
        "Source Text: " & .srcText & vbCrLf & _
        "Document URL: " & .url
    End With
    Debug.Print strErrText

  Dim s As String
  Dim r As String
  Dim i As Long
 
   s = ""
  For i = 1 To xPE.linepos - 1
    s = s & " "
  Next
  r = "XML Error loading " & xPE.url & " * " & xPE.reason
  Debug.Print r
    'show character postion of error; tired of counting
chars in xml file
  If (xPE.Line > 0) Then
    r = "at line " & xPE.Line & ", character " & xPE.linepos
& vbCrLf & _
         xPE.srcText & vbCrLf & s & "^"
  End If
  Debug.Print r
    MsgBox strErrText, vbExclamation
End Sub




--------  "C:\XML\AdviserDetails.xml"  save as UTF-8 not
ANSI in notepad

<?xml version="1.0" encoding="UTF-8"?>
<AdviserDetails ID="AD12345">
    <BusinessDetails>
        <BusinessName>"AD Business Name" </BusinessName>
        <AddressLine1>"AD Address Line 1" </AddressLine1>
        <AddressLine2>"AD Address Line 2" </AddressLine2>
        <Suburb>"AD Suburb" </Suburb>
        <State>"AD State" </State>
        <Postcode>"AD PostCode" </Postcode>
        <PhoneNumber>"AD Phone Number" </PhoneNumber>
        <Email>"AD Email" </Email>
        <FaxNumber>"AD Fax Number" </FaxNumber>
    </BusinessDetails>
</AdviserDetails>

Gustav Brock wrote:

>Hi Stuart
>
>Use A2002+.
>Have an internal table with your path, tblPath.
>
>To export:
>Application.ExportXML acExportTable , "tblPath",
"c:\mypath.xml"
>
>To import:
>CurrentDb.Execute "Delete * From tblPath"
>Application.ImportXML "c:\mypath.xml", acAppendData
>
>To read:
>strPath = DLookup("Path", "tblPath")
>
>Not quite sure how "easy" that is, though in total only
four code lines net are needed.
>However, if you need to store a bunch of user or app
settings it might be a nice method with all the advantages
of XML storage and exchange possibilities.
>
>/gustav
>
>  
>
>>>>stuart at lexacorp.com.pg 20-01-2006 00:48 >>>
>>>>        
>>>>
>On 19 Jan 2006 at 15:00, Charlotte Foust wrote:
>
>  
>
>>Actually, the XML files wind up with a structure very
similar to the ini
>>files but you don't need API calls to read them.
>>    
>>
>
>So how  would you create, then read an XML file to store a
backend server 
>path?
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list