Solved

Import XML file into existing table and update the record

Posted on 2014-10-04
1
462 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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 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