If violating PK, just move on and don't insert

[gmazza via AccessMonster.com]

Hey there,
I have a table (Table1) that I am getting tons of imported data inserted into,
with no primary keys.
Then I have another table (Table2), that has 2 PK's, that I am inserting into
from Table1.
Sometimes on the insert it fails because of a violation of the PK in Table2.
Is there anyway, other than limiting what goes into Table1, is there any
other way of when I go through my recordset of Table1, when I goto Insert, if
there is already a record with the same key in the table, I move onto the
next record, thus not spitting out an error message.
Thanks in advance!

 

[Jeff Boyce]

I'm not clear on what you have ...

Are you saying that your second table has two fields that you use together
as a multi-column primary key? ... because the notion of having TWO primary
keys is contradictory ... only one needed per table.

Without a clearer picture of what you are working with, I can only guess...

Is there a chance you could use a query to limit the items from Table1 that
you attempt to append into Table2?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[tbs]

if your query is "insert into table2... select from table1...",

insert into table2 (field1, field2, ...)
select field1, field2, ... from table1 where not exists (select 1 from
table2 where table2.field1 = table1.field1 and table2.field2 = table1.field2)

you might need to research abit on the syntax of using "exists" for access
sql jet if it's wrong.

 

[Dirk Goldgar]

Hey there,
I have a table (Table1) that I am getting tons of imported data inserted
into,
with no primary keys.
Then I have another table (Table2), that has 2 PK's, that I am inserting
into
from Table1.
Sometimes on the insert it fails because of a violation of the PK in
Table2.
Is there anyway, other than limiting what goes into Table1, is there any
other way of when I go through my recordset of Table1, when I goto Insert,
if
there is already a record with the same key in the table, I move onto the
next record, thus not spitting out an error message.

How are you doing your append? If you are executing an SQL statement, you
can control whether a duplicate key error will cause the whole append to
fail, or just the duplicate record(s). For example,

'------ start of code ------
Dim lngRecsAppended As Long
Dim lngRecsToAppend As Long
Dim strResults As String

With CurrentDb
' Insert records, ignoring duplicates or other errors.
.Execute "INSERT INTO Table2 SELECT * FROM Table1"
' How many records did we really append?
lngRecsAppended = .RecordsAffected
End With

lngRecsToAppend = DCount("*", "Table1")
MsgBox lngRecsAppended & " of " & lngRecsToAppend & " records were
appended."
'------ end of code ------

If, on the other hand, the call to .Execute had included the dbFailOnError
argument, then the append would have been an all or nothing operation, and
an error would have been raised if any records could not be appended (due to
duplicate keys or any other error.