Ac2002: Another ImportXML problem

[JotKa]

Hi all,

Using the Application object's ImportXML() method, we encountered another
problem:

Using acStructureAndData for the ImportOptions parameter ("do not create
structure"), ImportXML() imports the data into existing tables.

In our database these tables have got primary keys. If the XML data contains
records with identical values for the primary key column, no error is
reported, and only the first of these records is imported. This clearly is
not the desired behaviour. Instead, an error should be reported ("primary key
constraint violation" or something like that).

Thanks for any suggestions how to get it working.

JotKa

 

[Scott McDaniel]

Using the Application object's ImportXML() method, we encountered another
problem:

Using acStructureAndData for the ImportOptions parameter ("do not create
structure"), ImportXML() imports the data into existing tables.

In our database these tables have got primary keys. If the XML data contains
records with identical values for the primary key column, no error is
reported, and only the first of these records is imported. This clearly is
not the desired behaviour. Instead, an error should be reported ("primary key
constraint violation" or something like that).

In this case, you might be better off importing the data to temporary tables, then using VBA or SQL to move the data to
live tables after examining it (either manually or through code).

Access does provide the TransformXML method which you may be able to use to remove/move/massage data; I don't know, as
I've not used it. For tha matter, I've never been satisfied with the builtin XML Import features and unless the XML
import needs are VERY trivial, I've ended up writing my own XML import routines. This can be a pretty big task,
depending on what type of data is coming in, but once you get the base routines down and understand how to interact with
the file it's pretty straightforward.



Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[JotKa]

Thank you, Scott, for your reply:

Access does provide the TransformXML method which you may be able to use to
remove/move/massage data

Hmm ... are you sure that it exists already in Access 2002/XP? I could not
find it in the object browser.

Maybe you thought of the transform* methods of the MSXML2 library?

In this case, you might be better off importing the data to temporary tables,
then using VBA or SQL to move the data to
live tables after examining it (either manually or through code).

Okay, that might be a solution (with the temporary table not having any
constraints). But I don't want to pollute my database with such tables, so I
haven't tried to implement this.

You suggested to write my own XML routines. Since my needs ARE very trivial,
I would like to continue to use ImportXML.

But your response lead me to another solution that we will follow now: Prior
to the import itself we have to validate the XML file. And this already
creates a DOM which can be checked for duplicate key values.

Thanks for your time!

JotKa