By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

ACCESS VBA to Write Query Results to XML files

P: 25
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
Apr 9 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...
Expand|Select|Wrap|Line Numbers
  1. Function createTxtFile(path As String)
  2. Dim fs As Object
  3. Dim f As Object
  4.  
  5.     Set fs = CreateObject("Scripting.FileSystemObject")
  6.     Set f = fs.CreateTextFile(path, True)
  7.     f.Close
  8.  
  9. End Function
  10.  
  11. Function writeFiles()
  12. Dim db As DAO.Database
  13. Dim rs As DAO.Recordset
  14. Dim frfile As Integer
  15. Dim fileName As String
  16. Dim fline As String
  17. Dim i As Integer
  18.  
  19.    Set db = CurrentDb
  20.    Set rs = db.OpenRecordset("QueryName")
  21.    i = 1
  22.    rs.Move First
  23.    Do Until rs.EOF
  24.       createTxtFile("c:\file" & i " ".txt")
  25.       fileName = "c:\file" & i " ".txt"   
  26.       fline = rs!Field1 & ", " & rs!Field2 & ", " & rs!Field3 ' etc.
  27.  
  28.    'open the text file to be written to
  29.    frfile = FreeFile()
  30.    Open fileName For Output Access Write As #frfile
  31.  
  32.         Print #frfile, fline
  33.  
  34.     Close #frfile
  35.    i = i + 1
  36. Loop
  37.  
  38. End Function
  39.  
Apr 10 '07 #2

pks00
Expert 100+
P: 280
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


Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportRecsToXML()
  2.     Dim rs As DAO.Recordset
  3.     Dim qdf As DAO.QueryDef
  4.  
  5.     'Point to my temp query
  6.     Set qdf = CurrentDb.QueryDefs("qryABase")
  7.  
  8.     'Open recordset to my query
  9.     Set rs = CurrentDb.OpenRecordset("qryA")
  10.  
  11.     Do While rs.EOF = False
  12.  
  13.         'Set temp query to return that one record only
  14.         qdf.SQL = "SELECT * FROM qryA WHERE Field1 = " & rs!Field1
  15.  
  16.         'Now dump that temp query as a xml
  17.         Application.ExportXML acExportQuery, qdf.Name, "C:\tsdn\" & rs!Field1 & ".xml"
  18.  
  19.         rs.MoveNext
  20.     Loop
  21.     rs.Close
  22.     Set rs = Nothing
  23.  
  24. End Sub
  25.  
Apr 10 '07 #3

P: 25
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!
May 6 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
May 6 '07 #5

P: 25
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
May 7 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.