Solved

export access query to txt file using vba

Posted on 2010-09-01
4
468 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:bryanscott53
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 33580091
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
 

Author Comment

by:bryanscott53
ID: 33580646
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
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 33580976
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
 

Author Closing Comment

by:bryanscott53
ID: 33634330
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now