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."

re: Append new records to table syntax

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:0oq9k09jtpc1klml75bvbcnvtnvdjob6r1@4ax.com...[color=blue]
> 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."[/color]

re: Append new records to table syntax

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:0oq9k09jtpc1klml75bvbcnvtnvdjob6r1@4ax.com...[color=blue]
> 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."[/color]

re: Append new records to table syntax

On Mon, 13 Sep 2004 13:12:18 +1200, "Jeff Smith"
<NoWay@Not.This.Address> wrote:
[color=blue]
>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[/color]
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
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."