[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>

---

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"

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

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.