export access query to txt file using vba

I have the following code which i have tried to amend to give me the following output

<data>
<variable name="Platforms">
<row>
<column>Platform</column>
<column>Count</column>
</row>
<row>
<column>21CN</column>
<column />
</row>
<row>
<column>PDH</column>
<column>3</column>
</row>
<row>
<column>POWER</column>
<column />
</row>
<row>
<column>PSTN</column>
<column />
</row>
<row>
<column>PSTN AXE10</column>
<column>1</column>
</row>
</variable>
</data>

but instead of the above i get the text below. can you help me fix this?

<Data>
<variable name="Platforms">
<Row>
<Column> 21CN </Column>
<Column>
</Row>
</Variable>
</data>
<Data>
<variable name="Platforms">
<Row>
<Column> 21CN  SUPPORT TOOLS </Column>
<Column>
</Row>
</Variable>
</data>
<Data>
<variable name="Platforms">
<Row>
<Column> 21CN CORE/METRO NODE </Column>
<Column>
</Row>
</Variable>
</data>
Public Function PlatformsXML()
   Dim sSql As String
   Dim rs As New ADODB.Recordset
   Dim sFilename As String
   Dim fhFile As Integer
   Dim sLine As String

   sFilename = "C:\Users\700605949\desktop\textfile.txt"
   fhFile = FreeFile
   Open sFilename For Output As #fhFile

   sSql = "SELECT * FROM QryPlatformIncidentVolumesCurrent ORDER BY [Platform]"
   rs.Open sSql, CurrentProject.Connection, , adLockOptimistic, adCmdText
   Do While Not (rs.EOF)
      sLine = "<Data>" & vbCrLf & "<variable name=""Platforms"">" & vbCrLf & "<Row>" & vbCrLf & "<Column>" & _
rs![Platform] & "</Column>" & vbCrLf & "<Column>" & vbCrLf & "</Row>" & vbCrLf & "</Variable>" & vbCrLf & "</data>"
      Print #fhFile, sLine
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
   Close fhFile
End Function

Open in new window

bryanscott53Asked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
since I don't know what fields are returned by the QryPlatformIncidentVolumesCurrent  query, I used the most logical field name.  If you run the query, you should see the name of the column with the count value.  Change my code accordingly.
0
 
aikimarkCommented:
This should be close
Public Function PlatformsXML()
   Dim sSql As String
   Dim rs As New ADODB.Recordset
   Dim sFilename As String
   Dim fhFile As Integer
   Dim sLine As String

   sFilename = "C:\Users\700605949\desktop\textfile.txt"
   fhFile = FreeFile
   Open sFilename For Output As #fhFile

   sSql = "SELECT * FROM QryPlatformIncidentVolumesCurrent ORDER BY [Platform]"
   rs.Open sSql, CurrentProject.Connection, , adLockOptimistic, adCmdText

   Print #fhFile, "<Data>" & vbCrLf & "<variable name=""Platforms"">" & vbCrLf 

   Do While Not (rs.EOF)
      sLine = "<Row>" & vbCrLf & "<Column>" & _
rs![Platform] & "</Column>" & vbCrLf  
      Print #fhFile, sLine
      If IsNull(rs![Count] Then
          sLine =  "<Column />"& vbCrLf  
      Else
          sLine =  "<Column>" & rs![Count] & & "</Column>" & vbCrLf 
      End If 
      sLine = sLine & "</Row>" & vbCrLf
      Print #fhFile, sLine
      rs.MoveNext
   Loop
   Print #fhFile, "</Variable>" & vbCrLf & "</data>"
   rs.Close
   Set rs = Nothing
   Close fhFile
End Function

Open in new window

0
 
bryanscott53Author Commented:
aikimark, thanks. i should have said that the query is pullng together 2 tables. I get the following error message.
"Item cannot be found in the collection corresponding to the requested name or ordinal"

I am guessing it is because the the count field comes from the tblplatforms but i cannot seem to get the code to work, any ideas?
0
 
bryanscott53Author Commented:
Aikimark, many thanks for your help, apologies for the time it has taken me to get back to you.

I have amended a couple of things and it works great now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.