Access append - again


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Access append - again

  1. #1
    FloridaSteve Guest

    Access append - again


    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-)

  2. #2
    Paul Clement Guest

    Re: Access append - again

    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)

  3. #3
    FloridaSteve Guest

    Re: Access append - again


    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)



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center