Results 1 to 4 of 4

Thread: [Access 2007] VBA procedure for parsing XML database connection

  1. #1

    Thread Starter
    Addicted Member Smartacus's Avatar
    Join Date
    Oct 2009
    Location
    Deep South, USA
    Posts
    196

    Question [Access 2007] VBA procedure for parsing XML database connection

    I am working on a project that unfortunately is using a very old version of Sybase (11.5.1 ASE)

    I have linked their existing code via a Sybase ODBC connector but have now had success using ADO via jtdbc Java and be able to re-work all of that to one method.

    My mission is to not hard code the database server and DB name so I would like to use XML like app.config.xml to store the connection parameters for starters.

    Secondly, I want to use an XML parsing method that is not going to be version dependent or at least going to be compatible with XP thru 8 and greater. Since this is a community service organization there is not a lot of funding for many changes as you can tell by the Sybase version that was donated to them. Since they are already using the server for other antique programs right now the simple option of get a new server cant be done. I am already replacing common control usage to address those issues.

    Also to mention, this program uses Access Runtime only so many will not have an Office installation.

    The <ConnectionDefault> setting will determine which section to retrieve to build the connection string. The client can choose which ever works best and offer some flexibility while still using XP for a while.

    Code:
    <?xml version="1.0" encoding=”UTF-8”?>"
    <SETTINGS>
       <Connection>
         <ConnectionDefault Value="java"> </ConnectionDefault>
    	
           <java>
              <ConnectionType Name="ConnectMethod" Value= "JDBC"  />
              <ConnectionType Name="Driver" Value="net.sourceforge.jtds.jdbc.Driver" />
    	      <ConnectionType Name="ServerType="ASE-SQL" Value="sybase:" />
              <ConnectionType Name="ServerName="//TEST_MCG" />
    		  <ConnectionType Name="ServerTCP="//192.168.10.11" />
              <ConnectionType Name="PortName" Value="5000" />
    	      <ConnectionType Name="DataBase" Value="DI" />
              <ConnectionType Name="PropertySettings" Value="[;<property>=<value>[;...]]" /> 
           </java>
    
           <odbc>
    		  <ConnectionType Name="ConnectMethod" Value= "ODBC Direct"  />
    		  <ConnectionType Name="ServerType="ASE-SQL" Value="sybase:" />
              <ConnectionType Name="Driver" Value="{SYBASE SYSTEM 11}" />
              <ConnectionType Name="UserString" Value="Uid" />
    		  <ConnectionType Name="ServerName" Value="TEST_MCG" />
    		  <ConnectionType Name="DataBase" Value="DI" />
           </odbc>
    	   
    	   <system_dsn>
    	      <ConnectionType Name="ConnectMethod" Value= "ODBC DSN"  />
    	      <ConnectionType Name="ServerType="ASE-SQL" Value="sybase:" />
              <ConnectionType Name="Driver" Value="{SYBASE SYSTEM 11}" />
              <ConnectionType Name="UserString" Value="Uid" />
    		  <ConnectionType Name="ServerName" Value="TEST_MCG" />
    		  <ConnectionType Name="DataBase" Value="DI" />
    	   </system_dsn>
    	   
    	   <file_dsn>
    	      <ConnectionType Name="ConnectMethod" Value= "FILE DSN"  />
    	      <ConnectionType Name="FilePath="\config\dsn\" Value="sybase:" />
    	      <ConnectionType Name="ServerType="ASE-SQL" Value="Sybase:" />
              <ConnectionType Name="Driver" Value="{SYBASE SYSTEM 11}" />
              <ConnectionType Name="UserString" Value="Uid" />
    		  <ConnectionType Name="ServerName" Value="TEST_MCG" />
    		  <ConnectionType Name="DataBase" Value="DI" />
    	   </file_dsn>
          </Connection>
    </SETTINGS>
    ***************************************************
    Smartacus comes packaged "As Is With No Warranty"

    ************* Useful Links ******************
    FAQs: Index / Database Development / .NET CodeBank /
    Before Posting Here...MSDN

    MZTools (I love this tool when using VB6 - Free) /

  2. #2

    Thread Starter
    Addicted Member Smartacus's Avatar
    Join Date
    Oct 2009
    Location
    Deep South, USA
    Posts
    196

    Re: [Access 2007] VBA procedure for parsing XML database connection

    Well I have a working model going at least parsing part of what I want in the middle section. Thanks to MarkT for putting stuff out there for us to plagiarize.

    I am going to need to read the file and return DefaultConnection value and then set the section to pull in. I figured a quick read for the keyword value then do a select case to setup the connection string format. Ideas???

    Below is my current results
    **************************************************
    Code:
    Option Compare Database
    Option Explicit
    
    Private Type xmlData
        AttributeName As String
        AttributeValue As String
    End Type
    
    Private Sub Command0_Click()
        Dim xmlValues() As xmlData
        Dim strXML As String
        Dim i As Integer
        
        'Sample Data Format:
        strXML = "<java>"
        strXML = strXML & "<ConnectMethod=""JDBC""/>"
        strXML = strXML & "<Driver=""net.sourceforge.jtds.jdbc.Driver""/>"
        strXML = strXML & "<ServerType=""ASE-SQL""/>"
        strXML = strXML & "<TypeName=""sybase""/>"
        strXML = strXML & "<ServerName=""//TEST_MCG""/>"
        strXML = strXML & "<ServerTCP=""//192.168.10.11""/>"
        strXML = strXML & "<DataBase=""DI""/>"
        strXML = strXML & "<PortName=""5000"">"
        strXML = strXML & "<PropertySettings=""property=value""/>"
        strXML = strXML & "</java>"
        
        'File Format To BE
        
        'Java Connection - There will be system_dsn and also file dsn
        '    <java>
        '          <ConnectMethod="JDBC">
        '               <Driver="net.sourceforge.jtds.jdbc.Driver">
        '               <ServerType="ASE-SQL">
        '               <TypeName="sybase:">
        '               <ServerName="//TEST_MCG">
        '               <ServerTCP="//192.168.10.11">
        '               <DataBase="DI">
        '               <PropertySettings="[;<property>=<value>[;...]]">
        '       </java>
    
        xmlValues = GetLineData(strXML)
    
        For i = 0 To 8
            Debug.Print xmlValues(i).AttributeName & "-" & xmlValues(i).AttributeValue
        Next i
    
    End Sub
    
    Private Function GetLineData(ByVal xml As String) As xmlData()
        Dim i As Integer
        Dim xmlLineData(9) As xmlData
    
        For i = 0 To 8
            xmlLineData(i) = GetAttributeValue(i, xml)
        Next i
        GetLineData = xmlLineData
    
    End Function
    
    Private Function GetAttributeValue(ByVal AttributeIndex As Integer, ByVal xml As String) As xmlData
        Dim intStartPos As Integer
        Dim intEndPos As Integer
        Dim intLen As Integer
        Dim startTagPos As Integer
    
        Dim strFields(8) As String
        Dim strValue As String
    
        strFields(0) = "ConnectMethod"
        strFields(1) = "Driver"
        strFields(2) = "ServerType"
        strFields(3) = "TypeName"
        strFields(4) = "ServerName"
        strFields(5) = "ServerTCP"
        strFields(6) = "DataBase"
        strFields(7) = "PortName"
        strFields(8) = "PropertySettings"
    
        intStartPos = InStr(xml, strFields(AttributeIndex)) + Len(strFields(AttributeIndex)) + 1
    
        If AttributeIndex < UBound(strFields) Then
            intEndPos = InStr(intStartPos, xml, strFields(AttributeIndex + 1))
            intLen = intEndPos - intStartPos - 3
        Else
            intEndPos = InStr(intStartPos, xml, ">")
            intLen = intEndPos - intStartPos - 1
        End If
    
        strValue = Mid(xml, intStartPos, intLen)
    
        GetAttributeValue.AttributeName = Left(strFields(AttributeIndex), Len(strFields(AttributeIndex)))    '- 1)
        GetAttributeValue.AttributeValue = strValue
    
    End Function
    Return Code
    ConnectMethod-"JDBC"
    Driver-"net.sourceforge.jtds.jdbc.Driver"
    ServerType-"ASE-SQL"
    TypeName-"sybase"
    ServerName-"//TEST_MCG"
    ServerTCP-"//192.168.10.11"
    DataBase-"DI"
    PortName-"5000
    PropertySettings-"property=value"
    Last edited by Smartacus; Feb 28th, 2014 at 02:12 AM. Reason: forgot stuff
    ***************************************************
    Smartacus comes packaged "As Is With No Warranty"

    ************* Useful Links ******************
    FAQs: Index / Database Development / .NET CodeBank /
    Before Posting Here...MSDN

    MZTools (I love this tool when using VB6 - Free) /

  3. #3
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: [Access 2007] VBA procedure for parsing XML database connection

    Hi Smartacus,

    I'm sorry that I don't have time to write a detailed response, but I would recommend using MSXML to parse XML instead of re-rolling your own method. See here for more info:

    http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

    Google: vba msxml

  4. #4

    Thread Starter
    Addicted Member Smartacus's Avatar
    Join Date
    Oct 2009
    Location
    Deep South, USA
    Posts
    196

    Re: [Access 2007] VBA procedure for parsing XML database connection

    well that is what I ended up doing. I created a class for the xml import values then document60 and then to a collection to build the string. Just finishing up the last select case and my strings are done.

    It just got to be too much of a hassle and I could fore see a change in the format by the customer throwing me a loop. I am using the complete Error handling via msxml6 which really sped up the process. Some information on the tubular information highway says that MS will be supporting this for a long time and has no plans for v7, only additional improvements to current method so I feel better.

    Thanks.
    ***************************************************
    Smartacus comes packaged "As Is With No Warranty"

    ************* Useful Links ******************
    FAQs: Index / Database Development / .NET CodeBank /
    Before Posting Here...MSDN

    MZTools (I love this tool when using VB6 - Free) /

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width