-
Feb 26th, 2014, 10:47 PM
#1
Thread Starter
Addicted Member
[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>
-
Feb 28th, 2014, 02:08 AM
#2
Thread Starter
Addicted Member
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
-
Mar 5th, 2014, 03:03 PM
#3
Fanatic Member
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
-
Mar 6th, 2014, 08:58 PM
#4
Thread Starter
Addicted Member
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|