VBA code to fix key violation message

Hi

I have written some basic code to import some data into my database.
The problem is that I always get the key violation message "Microsoft
can't append all the records in the append query" which I expect as I
don't want to import duplicate data. This is fine. But the database
will be used by other people and I don't want it to come up. Is there
code in VBA I can use to automatically choose yes to this message??
Or would an SQL statement be better. I only have a little knowledge
of VBA and SQL so please bear that in mind.

Thanks

Sarah

re: VBA code to fix key violation message

It depends on what is driving this import process. If you are running a
query using DoCmd.RunSQL, then using DoCmd.SetWarnings = False/True before
and after this will prevent any messages. I prefer however to use the
Execute method of the database object (eg. db.Execute). This allows you to
use the Err object to trap those errors and handle them however you wish.

Mike Storr
www.veraccess.com



"Sarah" <rhodesar@yahoo.com> wrote in message
news:739cac7d.0401201056.559760d@posting.google.co m...[color=blue]
> Hi
>
> I have written some basic code to import some data into my database.
> The problem is that I always get the key violation message "Microsoft
> can't append all the records in the append query" which I expect as I
> don't want to import duplicate data. This is fine. But the database
> will be used by other people and I don't want it to come up. Is there
> code in VBA I can use to automatically choose yes to this message??
> Or would an SQL statement be better. I only have a little knowledge
> of VBA and SQL so please bear that in mind.
>
> Thanks
>
> Sarah[/color]

re: VBA code to fix key violation message

rhodesar@yahoo.com (Sarah) wrote in message news:<739cac7d.0401201056.559760d@posting.google.c om>...[color=blue]
> Hi
>
> I have written some basic code to import some data into my database.
> The problem is that I always get the key violation message "Microsoft
> can't append all the records in the append query" which I expect as I
> don't want to import duplicate data. This is fine. But the database
> will be used by other people and I don't want it to come up. Is there
> code in VBA I can use to automatically choose yes to this message??
> Or would an SQL statement be better. I only have a little knowledge
> of VBA and SQL so please bear that in mind.
>
> Thanks
>
> Sarah[/color]


Try something like:

DoCmd.SetWarnings False
DoCmd.TransferText... 'whatever your import routine is
DoCmd.SetWarnings True

and be _sure_ to set the Warnings to True in the error trapping for
your code, because otherwise, you'll turn if off globally.

re: VBA code to fix key violation message

Sarah,
The best way would be to import the data into a temp table and then
use your query to make sure that none of the duplicate recrds are
attempted.

To run your query without any error message turn the warnings off
before (and on after) - e.g.
docmd.setwarnings false
docmd.openquery "BlaBlaQueryName"
docmd.setwarnings true 'don't forget to do this!

HTH
Pachydermitis


rhodesar@yahoo.com (Sarah) wrote in message news:<739cac7d.0401201056.559760d@posting.google.c om>...[color=blue]
> Hi
>
> I have written some basic code to import some data into my database.
> The problem is that I always get the key violation message "Microsoft
> can't append all the records in the append query" which I expect as I
> don't want to import duplicate data. This is fine. But the database
> will be used by other people and I don't want it to come up. Is there
> code in VBA I can use to automatically choose yes to this message??
> Or would an SQL statement be better. I only have a little knowledge
> of VBA and SQL so please bear that in mind.
>
> Thanks
>
> Sarah[/color]