Bryan Scott
asked on
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>
<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
ASKER
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?
"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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I have amended a couple of things and it works great now
Open in new window