How to Convert Access Table to XML Based on User Input Values?

[s0me0nesmind1]

Hello everyone

Looking for some help. We have a table that describes input/values that the user will select. The user will input the possible values, then they will be coded in XML based on VBA.

I'm in need of some help in coding this on running a loop to check all of the fields to
1) See if there is a value in the field
2) If so, is it an element, or an attribute of an element
3) Have it write the XML tag

I'm very new to this, however I have seen through searching a lot of decoding from XML to VBA, but nothing about converting TO VBA using loops and going through an entire table of possible IF conditions.
Below is an example of the table - as said, some have values - others do not and should be ignored. Any help is appreciated!

example of the table: h.ttp://i.imgur.com/a7yBuNX.jpg

 

[mdlueck]

I would suggest you create a XML file from your data in VBA using MSXML objects. Presently I am working with MSXML 6 objects. I have posted a few times in this forum on the topic, so have shared some sample code which might assist you.

 

[s0me0nesmind1]

I would suggest you create a XML file from your data in VBA using MSXML objects. Presently I am working with MSXML 6 objects. I have posted a few times in this forum on the topic, so have shared some sample code which might assist you.

Right, so I have practiced with setting these with MSXML 6, an example of my code is below - my issue is with the writing of a loop to detect if the user inputed a value and if it's an element or attribute - if they did input a value write the MSXML.

Private Sub WriteXML()
   Dim objDom As DOMDocument
   Dim objRootElem As IXMLDOMElement
   Dim objMemberElem As IXMLDOMElement
   Dim objMemberRel As IXMLDOMAttribute
   Dim objMemberName As IXMLDOMElement
   Dim objnodelist As IXMLDOMNodeList
   
   Set objDom = New DOMDocument
   
   ' Creates root element
   Set objRootElem = objDom.createElement("Login")

 

[mdlueck]

my issue is with the writing of a loop to detect if the user inputed a value and if it's an element or attribute - if they did input a value write the MSXML.

Per your XML schema, are tags expected always, even if NULl value, or are XML tags all optional?

If required, then simply output all of the fields, and some may have no value sometimes.

If tags are optional, then test the VBA variable for vbNullString and in that case skip around putting that particular tag into the XML document.

 

[s0me0nesmind1]

Per your XML schema, are tags expected always, even if NULl value, or are XML tags all optional?

If required, then simply output all of the fields, and some may have no value sometimes.

If tags are optional, then test the VBA variable for vbNullString and in that case skip around putting that particular tag into the XML document.

No - we are looking to not have all the tags that have NULL and instead only write the XML tags that do have input value. I showed only part of the table - in total we have over 200 possible inputs/values/elements/attirbutes.

As far as using the vbNullString in conjunction with reading the table - could you possibly give me an example code? :banghead: Sorry, I'm hard headed.

 

[mdlueck]

As far as using the vbNullString in conjunction with reading the table - could you possibly give me an example code?

Sample code of reading an adoRS object safely:

      strFASRule = Nz(adoRS.Fields.Item("fasrule"), vbNullString)

Sample code of writing XML with conditional tags:

  'Create the FASRule element
  If strFASRule <> vbNullString then
    Set objXMLNodeFASRule = objXMLDOMDoc.createElement("FASRule")
    objXMLNodeFASRule.Text = strFASRule
    objXMLNodeRoot.appendChild objXMLNodeFASRule
  End If

 

[s0me0nesmind1]

Sample code of reading an adoRS object safely:

      strFASRule = Nz(adoRS.Fields.Item("fasrule"), vbNullString)

Sample code of writing XML with conditional tags:

  'Create the FASRule element
  If strFASRule <> vbNullString then
    Set objXMLNodeFASRule = objXMLDOMDoc.createElement("FASRule")
    objXMLNodeFASRule.Text = strFASRule
    objXMLNodeRoot.appendChild objXMLNodeFASRule
  End If

Thank you so much - but damn that is confusing me quite a bit.

So strFASRule (by the way, what is FAS Rule?) is testing if "FASRule" exists, otherwise it returns Null?

My other point in here is my lack of knowledge on reading in the Table. I'm used to reading in Excel sheets into database tables, but since this is simply reading it in and outputting it as XML, I'm not quite knowledgeable on that either.

Usually for reading in I have to establish my database with the below type of code. But since we are just reading this in - could you happen to enlighten me on reading this in for possible XML export?

I'm sorry, I'm sure this sounds awfully stupid

set db = DBEngine(0)(0)
 
CurrentDb.Execute ...<Continue>

 

[mdlueck]

So strFASRule (by the way, what is FAS Rule?) is testing if "FASRule" exists, otherwise it returns Null?

strFASRule is a VBA string data type variable. It hold attribute FASRule from both the database table and the XML file.

Database Table <---> VBA Variable <---> XML File

In my case, vbNullString is its value when it is NULL / does not exist / has not been defined / blank. So if it is NOT that value, then it must have some value worth saving, and adds that tag to the XML file.

Usually for reading in I have to establish my database with the below type of code. But since we are just reading this in - could you happen to enlighten me on reading this in for possible XML export?

set db = DBEngine(0)(0)
 
CurrentDb.Execute ...<Continue>

Perhaps that is code to read an Excel file as a database table? I have never done such, so have no knowledge to share.

And yes, you can get into trouble with Excel / VBA trying to read empty / NULL values. In my Excel VBA code I need to carefully tiptoe through reading cells. In place of Nz(), in Excel I use IsEmpty(). Also I have a collection of data type validators to insure that the value of some external data is of the correct data type to be held in a variable of a certain data type / processed as a certain data type.

Field Control Input Data Type Validation Functions
http://www.access-programmers.co.uk/forums/showthread.php?t=241516

 

[s0me0nesmind1]

I've actually had someone reccomend me the User Defined Function and laying this out into an array first.

Is it possible for someone to check and see if I am doing this right?

Private Type AccessDBRecord
    ID As Integer
    Level As String
    ElementOrAttribute As String
    Occurs As Integer
    DataType As String
    Description As String
    Type As Char
    Values As String
End Type
Function DBRecords() As Variant
    Dim MyNumbers(8) As Integer
    Dim MyAccessRecords(8) As AccessDBRecord
    Dim i As Integer
    
    'Set counter = 0
    i = 0
    
    'Run loop of assigning MyAccessRecords Array
    Do While Not rs.EOF
    MyAccessRecords(i).ID = rs!ID
    MyAccessRecords(i).Level = rs!Level
    MyAccessRecords(i).ElementOrAttribute = rs!Element/Attribute
    MyAccessRecords(i).Occurs = rs!Occurs
    MyAccessRecords(i).DataType = rs!DataType
    MyAccessRecords(i).Description = rs!Description
    MyAccessRecords(i).Type = rs!Type
    MyAccessRecords(i).Values = rs!Values
    'Counter ++
    i = i + 1
    Loop
'Write the XML Code
i = 0
objDom.Save ("C:\CSharpNoob2011.xml")