Access append - again

[FloridaSteve]

Hello, gurus,

This is a repeat with more information. I receive "table1" from an outside
source on a regular basis. I have a "table2" in an Access database with
corrected records. I wish to append records from "table1" that has additional
records plus the original records to "table2". I do not wish to over write
the corrected records. Each table has a record ID number. In "table2" the
record ID is primary key, indexed with no duplicates. "Table 1" record ID
is indexed but is not primary key. When I attempt to append, it seems that
Access is comparing the ID numbers. If the ID number already exists in "Table
2", the record in "table 1" will not append. So, now to the question - Is
is possible to programmatically to export the data from "table 1" to a delimited
text file and append the data from the delimited text file into "table 2"?
No doubt, this will create duplicate records, but sometimes, this is needed
to get updated data and then the duplicate record can be deleted.

Thanks for reading and responding to this question.

FloridaSteve 8-)

[Paul Clement]

On 20 Apr 2000 14:16:43 -0700, "FloridaSteve" <steve.wilson@smtp.cnet.navy.mil> wrote:

¤
¤ Hello, gurus,
¤
¤ This is a repeat with more information. I receive "table1" from an outside
¤ source on a regular basis. I have a "table2" in an Access database with
¤ corrected records. I wish to append records from "table1" that has additional
¤ records plus the original records to "table2". I do not wish to over write
¤ the corrected records. Each table has a record ID number. In "table2" the
¤ record ID is primary key, indexed with no duplicates. "Table 1" record ID
¤ is indexed but is not primary key. When I attempt to append, it seems that
¤ Access is comparing the ID numbers. If the ID number already exists in "Table
¤ 2", the record in "table 1" will not append. So, now to the question - Is
¤ is possible to programmatically to export the data from "table 1" to a delimited
¤ text file and append the data from the delimited text file into "table 2"?
¤ No doubt, this will create duplicate records, but sometimes, this is needed
¤ to get updated data and then the duplicate record can be deleted.
¤
¤ Thanks for reading and responding to this question.

So are you saying that something like this won't work for you:

INSERT INTO Table2 ( [record ID], Field1, Field2, Field3, Field4 )
SELECT Table1.[record ID], Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4
FROM Table1;


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[FloridaSteve]

Thank you, Paul. I can always count on a good quality response from you.
That is not to say that others who help don't provide good responses, but
every time I get one from you, it is dependable. Enough of that - now that
I have probably upset some folks without any intention of doing so. I have
yet to try the insert procedure. Have been using Access's VBA append and
it check the record number (primary key, indexed with no duplicates). Maybe,
if I set the index to have duplicates, it may work. Will give that a try
first. Then will give your approach a try to see how well I can code it.
Don't know if it is worth the effort anymore since VB may go away. ;( Thanks
again.

Florida Steve 8-)

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 20 Apr 2000 14:16:43 -0700, "FloridaSteve" <steve.wilson@smtp.cnet.navy.mil>
wrote:
>
>¤
>¤ Hello, gurus,
>¤
>¤ This is a repeat with more information. I receive "table1" from an outside
>¤ source on a regular basis. I have a "table2" in an Access database with
>¤ corrected records. I wish to append records from "table1" that has additional
>¤ records plus the original records to "table2". I do not wish to over
write
>¤ the corrected records. Each table has a record ID number. In "table2"
the
>¤ record ID is primary key, indexed with no duplicates. "Table 1" record
ID
>¤ is indexed but is not primary key. When I attempt to append, it seems
that
>¤ Access is comparing the ID numbers. If the ID number already exists in
"Table
>¤ 2", the record in "table 1" will not append. So, now to the question
- Is
>¤ is possible to programmatically to export the data from "table 1" to a
delimited
>¤ text file and append the data from the delimited text file into "table
2"?
>¤ No doubt, this will create duplicate records, but sometimes, this is
needed
>¤ to get updated data and then the duplicate record can be deleted.
>¤
>¤ Thanks for reading and responding to this question.
>
>So are you saying that something like this won't work for you:
>
>INSERT INTO Table2 ( [record ID], Field1, Field2, Field3, Field4 )
>SELECT Table1.[record ID], Table1.Field1, Table1.Field2, Table1.Field3,
Table1.Field4
>FROM Table1;
>
>
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)