467,188 Members | 1,419 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,188 developers. It's quick & easy.

Append new records to table syntax

As part of a macro, I'm trying to automate appending a table with new
records.

let's say the table 2 has some new records in it, but also has a lot
of identical records to table 1.

I would like to append table 1 with all of the new records, and do it
unattended in a macro.

Is there a way to specify in the append query to only append the
records that do not already match the linking fields in each table?

Since there can be no duplicated in the primary key, I know that I
could just run it and agree that some records can't be appended (the
same way you get rid of duplicates), but can I do this unattended with
no prompts? Otherwise I'm looking for a way to do this by just
specifying in the criteria just to update records where the "field1"
in table 1 does not match "field1" in table 2: but I've tried that and
I just get an error.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
Nov 13 '05 #1
  • viewed: 2599
Share:
3 Replies
Hi Larry,

Suggest writing an update query and then running the query from your macro.
Query would be something as simple as this assuming your tables have
identical fields:

INSERT INTO tblPerson
SELECT tblPersonDup.*
FROM tblPersonDup
WHERE (((tblPersonDup.PersonID) Not In (select PersonID from tblPerson)));

This appends records to tblPerson if PersonID does not already exist.

In your macro you'd have these commands:

SetWarnings No
OpenQuery Your query name here
SetWarnings Yes

HTH -Linda

"Larry Rekow" <larry@netgeexdotcom> wrote in message
news:0o********************************@4ax.com...
As part of a macro, I'm trying to automate appending a table with new
records.

let's say the table 2 has some new records in it, but also has a lot
of identical records to table 1.

I would like to append table 1 with all of the new records, and do it
unattended in a macro.

Is there a way to specify in the append query to only append the
records that do not already match the linking fields in each table?

Since there can be no duplicated in the primary key, I know that I
could just run it and agree that some records can't be appended (the
same way you get rid of duplicates), but can I do this unattended with
no prompts? Otherwise I'm looking for a way to do this by just
specifying in the criteria just to update records where the "field1"
in table 1 does not match "field1" in table 2: but I've tried that and
I just get an error.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

Nov 13 '05 #2
Hi Larry

Create a new query and select "Find Unmatched Query Wizard", follow the
instructions and save the query. Go into the design view of this query and
change it to an Append query, or use this query as the recordsource for your
Append query.

Jeff
"Larry Rekow" <larry@netgeexdotcom> wrote in message
news:0o********************************@4ax.com...
As part of a macro, I'm trying to automate appending a table with new
records.

let's say the table 2 has some new records in it, but also has a lot
of identical records to table 1.

I would like to append table 1 with all of the new records, and do it
unattended in a macro.

Is there a way to specify in the append query to only append the
records that do not already match the linking fields in each table?

Since there can be no duplicated in the primary key, I know that I
could just run it and agree that some records can't be appended (the
same way you get rid of duplicates), but can I do this unattended with
no prompts? Otherwise I'm looking for a way to do this by just
specifying in the criteria just to update records where the "field1"
in table 1 does not match "field1" in table 2: but I've tried that and
I just get an error.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

Nov 13 '05 #3
On Mon, 13 Sep 2004 13:12:18 +1200, "Jeff Smith"
<No***@Not.This.Address> wrote:
Hi Larry

Create a new query and select "Find Unmatched Query Wizard", follow the
instructions and save the query. Go into the design view of this query and
change it to an Append query, or use this query as the recordsource for your
Append query.

Jeff

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
Thanks to you, Jeff, and squirrel for putting me right.

My brain was about to break (and still might!).

Laterz,

Larry

- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by JMCN | last post: by
2 posts views Thread by anita | last post: by
5 posts views Thread by Michael C via AccessMonster.com | last post: by
4 posts views Thread by pmacdiddie@gmail.com | last post: by
1 post views Thread by hr833 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.