XML beginner - Update FoxPro dbf using Access/XML

[cw via AccessMonster.com]

I have been given an XML API that allows real-time updating of our FoxPro
backend database.
Using XML on my Access Form seems like a magical solution and I want to use
this project as a primer on understanding XML & Access 2007.

Here is what I have so far:
--------------------------------------------
<ROOT>
<CUST Action="Update" ACCT="3902">
<TROUBLE Action="Insert">
<NUMBER>9991234567</NUMBER>
<REPORTED>03-09-2007</REPORTED>
<RESOLVED>03-11-2007</RESOLVED>
<TICKET_NBR>2777</TICKET_NBR>
<SEV_CODE>RED</SEV_CODE>
<REC_FROM>John</REC_FROM>
<RPT_TO>Fred</RPT_TO>
<PROBDESC>Test Trouble Ticket Generated from the XML API</PROBDESC>
<PROBLEM>This is a Test Trouble Ticket from the XML API</PROBLEM>
<SOLUTION>This Test Ticket is now fixed and all is well!</SOLUTION>
</TROUBLE>
</CUST>
</ROOT>
-------------------------------------------------
This code, when sent to the XML API using a simple HTML form in IE6 and
<input type="hidden" name="Action" value="Update"> will successfully update
FoxPro with the ticket details. So using my sample HTML form I can manually
type new values in the code above.

Here is the resulting XML code:
---------------------------------------------
<?xml version="1.0" ?>
- <ROOT>
- <CUST Action="Result" ACCT="4089" ResultCode="OK" ResultText="OK">
<ACCT>4089</ACCT>
- <TROUBLE Action="Result" ResultCode="OK" ResultText="OK">
<NUMBER>6182520492</NUMBER>
<REPORTED>04-09-2007</REPORTED>
<RESOLVED>04-11-2007</RESOLVED>
<TICKET_NBR>3778</TICKET_NBR>
<SEV_CODE>YEL</SEV_CODE>
<REC_FROM>jalexander</REC_FROM>
<RPT_TO>acarian</RPT_TO>
<PROBDESC>Test Trouble Ticket Generated from the XML API</PROBDESC>
<PROBLEM>This is a Test Trouble Ticket from the XML API</PROBLEM>
<SOLUTION>This Test Ticket is now fixed and all is well!</SOLUTION>
</TROUBLE>
</CUST>
</ROOT>
-----------------------------------------------------------------

How would I do the same thing in Access?:
1 - Display a Customer on my Access form
2 - User updates the Text boxes for REC_FROM, RPT_TO, PROBDESC, SOLUTION etc
3 - Clicks an UPDATE button to run the XML code
4 - Which assigns these values to the XML code/fields & updates FoxPro?

I apologize for not understanding XML in Access but I have never used it
before at all.

Thanks in advance for your ideas & suggestions.
cw

 

[Aaron Kempf]

you can use updateGrams in SQL Server, or the Xml BULKLOAD component

or the XML datatype

Access is for friggin cry babies


anyone using Access MDB or ACCDB should be fired and then spit upon


ADP won the war, kids

 

[cw via AccessMonster.com]

Aaron, Thanks for the response.
The FoxPro database is a legacy application that will not be changing anytime
soon.
It works fine for it's intended use as a Billing database and can be accented
by Access.

As far as Access goes, it too is a great help as a rapid Front End
application builder to manipulate legacy data in these older Back Ends.

And that was my thought concerning the XML API. It already works great in an
HTML form, so I know that it could probably be accented further using a
custom Access form.

My idea is basically to create a Form in Access that has "variables", unbound
& bound Text boxes for each of the XML data fields.
Once they are filled in on the Access form, and the Command button is clicked,
the "variable" values are "Inserted" into the XML code listed above,
replacing the old default values.

With that code populated with the new values, I then would "post" it using
the XML API that writes it to the FoxPro database.

The XML API does all of the work, it interfaces with FoxPro via an IP Address
and Port number. All I have to do is "feed" it the XML text file with the
updated data fields from Access.

Anyone else have any ideas..
Thanks again,
cw

you can use updateGrams in SQL Server, or the Xml BULKLOAD component

or the XML datatype

Access is for friggin cry babies

anyone using Access MDB or ACCDB should be fired and then spit upon

ADP won the war, kids

I have been given an XML API that allows real-time updating of our FoxPro
backend database.

[quoted text clipped - 58 lines]

Thanks in advance for your ideas & suggestions.
cw