Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import XML file into existing table and update the record

Posted on 2014-10-04
1
Medium Priority
?
608 Views
Last Modified: 2014-10-05
I have a XML and a XSD file on my desktop that I want to use to UPDATE an existing record in a table in my Access 2010 database.

What is the best way to achieve this?

--Steve
0
Comment
Question by:SteveL13
1 Comment
 
LVL 7

Accepted Solution

by:
Gauthier earned 2000 total points
ID: 40361393
http://msdn.microsoft.com/en-us/library/office/ff823157(v=office.14).aspx

The best way will depend on any integrity link from the table to update to the rest of your  database
Assuming we cannot temporarily delete that table or it's content, we have those solution:

BEST Solution (If you control the export process)
- Make the export/import from a temporary table copy you can delete safely.
step 1 Delete existing temporary table if it exists
step 2 Run the importXML with the parameter acStructureAndData.
step 3 Execute a simple update query.

If the table exist is the same name, cannot be deleted and the record to update also cannot be deleted beforehand:

importXML with acAppendData will fail (duplicate primary key)
importXML with acStructureAndData willproduce a new unknown to your code tablename.
you can work arround that in this way:

step 1 delete the importErrors (or count rows)
step 2 Make a list of existing table name.
step 3 Run the importXML with acStructureAndData
step 4 Compare current table name with earlier list.
step 5 Check the importErrors table.
step 6 Create an update query using the found name.
step 7 Run the update query
step 8 Delete the imported table.
step 9 Report any errors to the user.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question