ACCESS VBA to Write Query Results to XML files

[GreggaR0und]

Hello;

I am trying to use Access VBA to write each record in a query to its own individual XML file. That is, each record represents one XML file which will then be sent to a SharePoint Form Library.

I am unsure how to tackle this problem. I tried to send my QueryResults to InfoPath, but all the records appear as repeating sections in a single XML file. Therefore, that choice doesn't seem to work. So now I'm wondering if I can bypass the InfoPath step and just write directly out of Access to the SharePoint Form Library.

For Reference, I have the MS-ACCESS 2003 Bible by Wiley Press.

Thanks,
Gregg

[MMcCarthy]

Hi Gregg

I'm not sure how to connect to the sharepoint form library although someone else may know.

However, to write out records individually to a series of text file for example you could do the following ...

Function createTxtFile(path As String)
Dim fs As Object
Dim f As Object
 
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.CreateTextFile(path, True)
    f.Close
 
End Function
 
Function writeFiles()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frfile As Integer
Dim fileName As String
Dim fline As String
Dim i As Integer
 
   Set db = CurrentDb
   Set rs = db.OpenRecordset("QueryName")
   i = 1
   rs.Move First
   Do Until rs.EOF
      createTxtFile("c:\file" & i " ".txt")
      fileName = "c:\file" & i " ".txt"   
      fline = rs!Field1 & ", " & rs!Field2 & ", " & rs!Field3 ' etc.
 
   'open the text file to be written to
   frfile = FreeFile()
   Open fileName For Output Access Write As #frfile
 
        Print #frfile, fline
 
    Close #frfile
   i = i + 1
Loop
 
End Function
 

[pks00]

If u have the 2003 Bible, I assume u are using Acces 2003 ?
Ok, u can use ExportXML function

this is what u can do
lets say u had a query called qryA and it has this

select field1,field2,field3,field4 from mytable

where field1 is the primary key

What u need to do is create a new query, call it say, qryABase
all it has is SELECT * FROM QryA

Here is sample code which now goes thru qryA, setting and exporting qryABase as xml

Public Sub ExportRecsToXML()
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
 
    'Point to my temp query
    Set qdf = CurrentDb.QueryDefs("qryABase")
 
    'Open recordset to my query
    Set rs = CurrentDb.OpenRecordset("qryA")
 
    Do While rs.EOF = False
 
        'Set temp query to return that one record only
        qdf.SQL = "SELECT * FROM qryA WHERE Field1 = " & rs!Field1
 
        'Now dump that temp query as a xml
        Application.ExportXML acExportQuery, qdf.Name, "C:\tsdn\" & rs!Field1 & ".xml"
 
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
 
End Sub
 

[GreggaR0und]

Okay, this worked really well. The only issue now is that the Parameter Value dialog box appears for every single record being exported. How can I satisfy the value so that I don't need to enter it each time? Thanks!

[MMcCarthy]

Okay, this worked really well. The only issue now is that the Parameter Value dialog box appears for every single record being exported. How can I satisfy the value so that I don't need to enter it each time? Thanks!

Put the value in a textbox on a form. Enter the value on the form and keep the form open when running the code. Change the criteria of the query from an input box to reference the textbox on the form.

Mary

[GreggaR0und]

Hi;

Thanks for the solution. I ended up doing it another way because I wasn't sure how to manipulate the input user forms. What I did instead was I wrote a single record to a temp table that I created within Access. I then used the temp table as the DataSource argument for the ExportXML object. Then I delete the temp record and read the next record from the primary table until EOF.

Not sure how valid an approach that is, but it seems to be working for my needs right now.

Thanks,
Gregg