Append Query NOT to insert rows that are already there 1

[darinmc]

Hi
I have this simple append query below, I want all rows from TABLE2 to be inserted into table1 which will EXCLUDE all DifID already in table 1 (Main)
Basically, ONLY insert new rows.
The below does work just appending everything.
How would I write the WHERE clause?

Sql1 = "INSERT INTO table1 ( Name, Surname, Wend, DifID )" & _
"SELECT table2.Name, table2.Surname, table2.Wend, table2.DifID " & _
"FROM table2;"

Hope u can help
Thx
Darin

 

[PHV]

EXCLUDE all DifID already in table 1
Have you tried this ?
Sql1 = "INSERT INTO table1 ( Name, Surname, Wend, DifID )" & _
"SELECT Name, Surname, Wend, DifID " & _
"FROM table2 WHERE DifID NOT IN (SELECT DifID FROM table1)"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darinmc]

Thx PH
Worked fine

 

[darinmc]

Is there a way to display how many records were inserted using a msgbox??
Thx Darin

 

[PHV]

You may try something this:
With yourDatabaseObject
.Execute Sql1
MsgBox .RecordsAffected & " record(s) inserted"
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darinmc]

thx PHV
I will post the code as it is... I couldn't get it to work and I am trying to use an append query... The sample just uses 2 different SQL statements
I would like to show the number of appended records in the last MsgBox if possible.

Private Sub cmdAppend_Click()
DoCmd.SetWarnings False
'On Error GoTo cmdAppend_Err
Dim Sql1, Sql2 As String
Sql1 = "INSERT INTO table1 ( Name, Surname, Wend, DifID )" & _
"SELECT Name, Surname, Wend, DifID " & _
"FROM table2 WHERE DifID NOT IN (SELECT DifID FROM table1);"

Sql2 = "INSERT INTO table1 ( Name, Surname, Wend, DifID )" & _
"SELECT table3.Name, table3.Surname, table3.Wend, table3.DifID " & _
"FROM table3 WHERE table3.DifID NOT IN (SELECT DifID FROM table1);"

DoCmd.RunSQL Sql1
DoCmd.RunSQL Sql2

DoCmd.SetWarnings True
MsgBox "Files Copied to Table 1"

End Sub

 

[PHV]

Replace this:
DoCmd.RunSQL Sql1
DoCmd.RunSQL Sql2
with this:
Set Db = CurrentDb
With Db
.Execute Sql1
lngCount = .RecordsAffected
.Execute Sql2
lngCount = lngCount + .RecordsAffected
End If
MsgBox lngCount & " rows copied to Table 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Andrzejek]

PHV, your code on how many records where inserted should be:

Dim i As Integer

cnConn.Execute Sql1, i
[green]'cnConn - connection to db[/green]
MsgBox i & " record(s) inserted"

You may try:
DoCmd.RunSQL Sql1, i
MsgBox i & " record(s) inserted"
DoCmd.RunSQL Sql2, i
MsgBox i & " record(s) inserted"

but I do not know if DoCmd will return i

---- Andy

 

[darinmc]

its got an error,

end if without block if???
ALSO, should i also include the Dim statement above?
Thx
Darin

 

[PHV]

Sorry for the typo:
Dim Db As DAO.Database, lngCount As Long
Set Db = CurrentDb
With Db
.Execute Sql1
lngCount = .RecordsAffected
.Execute Sql2
lngCount = lngCount + .RecordsAffected
End [!]With[/!]
MsgBox lngCount & " rows copied to Table 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darinmc]

Thx PHV, works perfectly, will adapt it to the correct query..