By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

VBA code to fix key violation message

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <rh******@yahoo.com> wrote in message
news:73*************************@posting.google.co m...
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

Nov 12 '05 #2

P: n/a
rh******@yahoo.com (Sarah) wrote in message news:<73*************************@posting.google.c om>...
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

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.
Nov 12 '05 #3

P: n/a
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
rh******@yahoo.com (Sarah) wrote in message news:<73*************************@posting.google.c om>...
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.