add new records to exported xml file

[TIbbs]

I am not sure if that is possible, but I am running a script in MS Access 2003 that retrieves a series of records from a query and outputs them to an external xml file.
I created the tree and the child records. this is the format on the xml document:

<Company>
-<Products/>
-<Customers>
-<Customer>
-<CustomerDetail>
<detail1</detail1>
<detail2></detail2>
</CustomerDetail>
-</Customer>
-<Customer>
-<CustomerDetail>
<detail1</detail1>
<detail2></detail2>
</CustomerDetail>
-</Customer>
</Company>

I want to add new records to that tree as they are saved in Access.
My problem is that when new orders are created my script adds the whole tree all over again to the bottom of the previous outputed tree.

The xml file cannot be deleted and create again as it is required for another application to read and output to another application at regular intervals.
Here is the code that slightly changed from DCrake.

Public Function CreateXMLtree2()
Dim SQLParent As String
Dim SQLChild As String
Dim rst As New ADODB.Recordset
Dim qry As ADODB.Recordset
Dim XMLbody As ADODB.Recordset
Dim ChildID As String
Dim XMLPath As String
Dim ParentID As String
Dim Customers, Products, SalesOrders, Invoices As ADODB.Recordset
Dim CustSQL, ProdSQL, SOPSQL, POPSQL, TranSQL, InvSQL As String
Dim cnn As ADODB.Connection
Dim CustID, CompanyName, AcctRef, VatNo As String
Dim Title, Forename, Surname, Add1, Add2, Add3, Town, PCode, County, Country, Phone, fax, Mobile, Email  As String

Set cnn = CurrentProject.Connection
XMLPath = "C:\downloadtest.xml"

SQLParent = "SELECT * FROM Orders;"



rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open SQLParent, cnn, adCmdText


Open XMLPath For Append As #1

 Print #1, "<Company>"
 
'Create Products Parent Tree

Set Products = New ADODB.Recordset
Products.CursorType = adOpenStatic
Products.LockType = adLockOptimistic

Print #1, "<Products>" & "</Products>"

'Create Customers Parent Tree

CustSQL = "SELECT Company.* FROM Company WHERE (((Company.Updated)=False));"

Set Customers = New ADODB.Recordset
Customers.CursorType = adOpenStatic
Customers.LockType = adLockOptimistic
Customers.Open CustSQL, cnn, adCmdText

Print #1, "<Customers>"

Do While Not Customers.EOF


CustID = Customers.Fields("ID").Value
Forename = Customers.Fields("BillingForename").Value
Surname = Customers.Fields("BillingSurname").Value


Print #1, "<Customer>"
Print #1, "<ID>" & CustID & "</ID>"
Print #1, "<CompanyName>" & "</CompanyName>"
Print #1, "<AccountReference>" & "</AccountReference>"
Print #1, "<VatNumber>" & VatNo & "</VatNumber>"

Print #1, "<CustomerInvoiceAddress>"
Print #1, "<Title>" & "</Title>"
Print #1, "<Forename>" & Forename & "</Forename>"
Print #1, "<Surname>" & Surname & "</Surname>"
Print #1, "<Company>" & "</Company>"
Print #1, "<Address1>" & "</Address1>"
Print #1, "<Address2>" & "</Address2>"
Print #1, "<Address3>" & "</Address3>"
Print #1, "<Town>" & "</Town>"
Print #1, "<Postcode>" & "</Postcode>"
Print #1, "<County>" & "</County>"
Print #1, "<Country>" & "</Country>"
Print #1, "<Telephone>" & "</Telephone>"
Print #1, "<Fax>" & "</Fax>"
Print #1, "<Mobile>" & "</Mobile>"
Print #1, "<Email>" & "</Email>"
Print #1, "</CustomerInvoiceAddress>"

Print #1, "<CustomerDeliveryAddress>"
Print #1, "<Title>" & "</Title>"
Print #1, "<Forename>" & Forename & "</Forename>"
Print #1, "<Surname>" & Surname & "</Surname>"
Print #1, "<Company>" & "</Company>"
Print #1, "<Address1>" & "</Address1>"
Print #1, "<Address2>" & "</Address2>"
Print #1, "<Address3>" & "</Address3>"
Print #1, "<Town>" & "</Town>"
Print #1, "<Postcode>" & "</Postcode>"
Print #1, "<County>" & "</County>"
Print #1, "<Country>" & "</Country>"
Print #1, "<Telephone>" & "</Telephone>"
Print #1, "<Fax>" & "</Fax>"
Print #1, "<Mobile>" & "</Mobile>"
Print #1, "<Email>" & "</Email>"
Print #1, "</CustomerDeliveryAddress>"




Print #1, "</Customer>"
Customers.MoveNext
Loop
Print #1, "</Customers>"
Customers.Close
Set Customers = Nothing



 
Print #1, "</Company>"
rst.Fields("Updated").Value = True
rst.Update
rst.MoveNext



Close #1
rst.Close
Set rst = Nothing

CurrentDb.Execute "UPDATE Orders SET Orders.Updated = True;"

End Function

 

[nIGHTmAYOR]

The xml file cannot be deleted and create again as it is required for another application to read and output to another application at regular intervals.
Here is the code that slightly changed from DCrake.

Welp for a start you should have used "file system object" since you plan on creating / editing / formating text files , however i hate to see all that code thrown away so why not use file system object to just clear the file before Open function can APPEND to file , just enter the following code anywhere before "Open XMLPath For Append As #1" so here goes :

Dim fs, fname
Set fs = CreateObject("Scripting.FileSystemObject")
Set fname = fs.CreateTextFile("C:\downloadtest.xml", True)
fname.WriteLine ("")
fname.Close
Set fname = Nothing
Set fs = Nothing

Now feel free to learn more about FSO to learn how to modify your script to use it rather than the limited Open command.