MS Access How To Change a XML File Element Value With a Value In a Field From a Table

Thanks Pat. You said

Alternatively, you can import the XML file, update the imported data, and then export it again.

Could you give me an example please?

I know how to import the XML file with VBA:

Application.ImportXML "C:\Users\Shipper\Documents\Access XML Save Files\EbayGetSessionIDCallReturnFile.xml", acStructureAndData 'This imports the XML file into Access into a table so the SessionID can be extracted. 

Select allOpen in new window

But I don't know How to update or Export with VBA.

I can't give you an example without knowing exactly what you are trying to do.  Once you import the table, you can run an update query if the update you need to make applies to all rows of a set.  If you want to update one specific record, you will need to know how to find it.  In this case, the user might want to locate the record and update it.  Then when he's done, you would export the file again.

All in all, this sounds dangerous.  Why is your data stored in XML and why do you need to update it?  Generally Access uses XML as import or export.  You can't simply link to it as you can a relational database such as SQL server and update it.  XML is a text file and so you would need to use text file methods to update it.  Text files can't actually be updated either although you can append rows to the end of them.  To work with a text file that needs to be updated, you need to open the file, read it record by record and write out each record to the output file whether or not you updated it.  Or perhaps your logic requires purging the data so you read the input file record by record and write out the records you want to keep and bypass writing the ones you want to "delete".

If you actually need to manipulate the text file and you are not capable of writing the necessary VBA to process the file, you should probably initiate a gig request and pay someone to code it to your specification.  But for now, lets flesh out what you need to do and that will help to formulate a gig request that can be satisfied.

If I set up a table in Access called "FetchToken" and in FetchToken there is a field called "SessionID". Can I update the field SessionID in the table FetchToken with the value from the other Table "GetSessionIDResponse" Field "SessionID" ???

After that just Export to a XML.

If so how do you update the field in Table 2 with Table 1s field value?

Thanks for the help!

This is in XML because it is making a API call to a 3rd Party on their website.

You can only do this if you have a field to join the two tables on.  If so,
Backup the database before you create any new update.
start a new query.  
Select both tables from the list.  
Close the list.  
Draw a join line between the two tables.
Change the query type to Update.
Select the field you want to update in the FetchToken table.
In the Update To cell, type the name of the field from the other table qualifying it with the table name in the format:

tblYourTableName.SessionID


Save the query.
Run it.

Thanks Pat for the help. How I originally had this set up (And planned for it to work) was GetSessionIDResponse table was imported from an XML file.  GetSessionIDResponse XML file contained the SessionID code which I needed.

I then would need to transfer this SessionID to the FetchToken XML file (Whether that was directly or import XML, Update, and then Export XML) After that the GetSessionIDResponse table and Fetch Token table would be deleted completely after that was done.

I only need this SessionID Code briefly then it can be deleted. But you said

You can only do this if you have a field to join the two tables on.

This brought up a different situation and I would really appreciate your input to head me in the correct direction please.

Should I keep these tables all the time in Access?
If so would I just append the XML file data to the tables and link on the Autonumber ID? (Because each table would get a new record at the same time. Never separately.)
Then when I export to a XML is there a way to leave the Autonumber ID field behind out of the XML file?

I truly appreciate it. I always feel like I am right there but a million miles away ;)

I don't know if you have time but here is a link to what I am exactly trying to do (Option1):
Step 1 GetSessionID
Step 2 Direct User to eBay Sign-In
Step 3 Call FetchToken

I have had success up to step 3

http://developer.ebay.com/DevZone/guides/ebayfeatures/Basics/Tokens-MultipleUsers.html#OptionClientDesktopApplications

I'm getting ready to leave to play Bridge so I can't look at anything now but you can't join two tables on their autonumbers.  Autonumbers are not controlled by you and there is too much danger in them getting out of sync.  If there is no natural key, you're in a bind.  How were you planning on matching the tables?  Surely there is something in tblA that tells you what row in tblB you want to update.

Not exactly. I had just planned to have a single record only in each table then afterwards delete the tables. Then repeat as necessary.  It was just the only plan I could come up with to automate with VBA.

If there is only ever a single row then don't use an autonumber.  Use a numeric field with a default value of 1 and a validation rule that only allows 1 as the value.  That way, only one record can ever be added to the table.

Thanks pat. I'm glad we are on the same page. I have been working on this for a few hours today and that is what I did. I added the number field with 1 as the default. The validation is a great idea. In the validation cell would I just place a 1 or is there more to it? Thanks for the help.

Ok I have found a way for what I need and I found it here on Experts-Exchange.

What I did was imported the XML file GetSessionIDResponse into Access into a table. After that I had a standard XML file that needed only one Element value changed and updated with the value in the field SessionID in the Table GetSessionIDResponse . The code I found and changed a little was:

Dim iCount As Integer
Dim TmpFile As String
Dim tmp As Variant

Open "C:YOUR DIRECTORY.xml" For Input As #1
    Do Until EOF(1)
        Line Input #1, tmp
        iCount = iCount + 1
        If iCount = 4 Then tmp = "<SessionID>" & DLookup("SessionID", "GetSessionIDResponse", "") & "</SessionID>" 'This is where you put the line number to be change. Right after If iCount =
        TmpFile = TmpFile & tmp & vbCrLf
    Loop
Close #1

Open "C:\YOUR DIRECTORY.xml" For Output As #1
Print #1, TmpFile
Close #1

Select allOpen in new window

Thanks for the help Pat!

You're welcome but if you use that code, don't hard-code the path.  Either give the user the option of specifying the path each time if it could change or make a table that holds the default path and allow the user to change that if it becomes necessary.  You can use the two in combination by automatically populating the folder with the saved default but allowing the user to override if necessary.