Ignore/Suppress 31550 Error in VBA (ImportXML)

[Heatshiver]

I have the following code for importing and appending table data:

Application.ImportXML _
 DataSource:="c:\TEST.xml", _
 ImportOptions:=acAppendData

The code works perfectly. It gives the error that I am trying to create duplicate index fields, and then create an "ImportErrors" table; which is completely correct as far as how it is working.

I would like to ignore/suppress the 31550 Error it gives after importing the table data. I have tried several solutions, including naming a Const and making that the error number, then adding Response = acDataErrContinue in the Sub Form_Error without success. How do I do this?

I can create code to delete the table it creates, but I may want that for my own purposes later down the road.

 

[PaulSpell]

If you want to trap the error can't you use:


On Error Goto errTrap
.........
.........
.........

errTrap:

if err.number= 31550 then

...do something​

end if

======================

or use Resume Next ?

What is it that you want the code to do when you encounter this error?

 

[pr2-eugin]

I would like to ignore/suppress the 31550 Error it gives after importing the table data.

I do not think Errors are meant to be suppressed, but dealt with.. specially this one.. Index's help in sorting, accessing the data from the table relatively faster.. That is the reason behind them being unique (not always though !)

It gives the error that I am trying to create duplicate index fields

Try to identify, what you wish to do if you find a duplicate..

 

[Heatshiver]

@PaulSpell - Tried that, but didn't work in the sub form error...

@pr2-eugin - I agree, the thing is I'm not sure how to modify the code to take out index keys and whatnot. If you have a suggestion I'd be more than willing try.

Thanks for the help!

 

[PaulSpell]

Sorry I'm still not clear on what you're trying to do.

Are you trying import records to a table that has a primary field and if the record you're trying to import has creates a duplicate primary key, you want to do something else?

 

[Heatshiver]

I have about 10 tables or so that I export to XML. There is one main table that links them all. I believe that when it imports the XML file and finds primary index values already in the database, it creates this error.

The error doesn't bother me, but it will to the end-users. I'm just trying to find a way to get rid of that error. If this requires a different method to exclude those fields, I would like to try it, but I don't know where to find that information as of yet.

Thanks.

 

[PaulSpell]

I have about 10 tables or so that I export to XML. There is one main table that links them all. I believe that when it imports the XML file and finds primary index values already in the database, it creates this error.

The error doesn't bother me, but it will to the end-users. I'm just trying to find a way to get rid of that error. If this requires a different method to exclude those fields, I would like to try it, but I don't know where to find that information as of yet.

Thanks.

Your Primary Key should be unique and therefore avoid this problem. Access is actually doing what it is supposed to do. Rather than trying to deal with this problem using code, I think you need to re-define your Primary Key so that it is truly unique.

Alternatively you could import the data to a temp table initially, then compare the temp table with the target table to identify any records with duplicate Primary Key values and deal with them appropriately. You can then append the non-duplicate records to the target table.

Does this help?

 

[gemma-the-husky]

you do need to understand your data.

if you are importing records that already exist (ie the PK exists) - then what do you want to happen?

ignore them? check for differences? report them to you?

you need to decide and program accordingly. the error code just determines the mature of the error

 

[Heatshiver]

@PaulSpell - that sounds perfect. Do you know any sites that could show me how took do this? Thanks.

@gemma - the easiest thing would be to ignore the errors, as that would seem not only the easiest task, but would give me a table of date and time when imports occurred.

However, Paul's idea is probably the best solution. I saw someone post a similar idea but not much on how to actually do it.

Thanks for the help!