Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing updates (Access 2000)

    Is it possible to import updated records into an existing table? I have a table with several fields that together define a unique record. On a periodic basis, I receive an updated file which I need to import. The updated file will contain both new records and updates to existing records. The updates - for instance - may include a payment on an account. Ideally, I would like to import the new file - adding new records - and updating the existing ones (Access would now the difference based on the "several fields together define a unique record" mentioned above).

    I am somewhat of an Access novice with NO VBA experience in Access (though have done a little tinkering with VBA in Excel).

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing updates (Access 2000)

    Yes, you simply need to tell Access which table to append the records to. However, If the records that come in don't have the same *shape* as the records in your table, then you'll need to append them to a new table and then use an append query to get them into your existing table.
    <hr>The updates - for instance - may include a payment on an account. <hr>
    You can't update an existing table in an import, you can only append. Depending on the datasource, though, you could possibly link it instead of importing it. That would allow you to run append or update queries from it as appropriate
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing updates (Access 2000)

    the general method would be to import your data into a staging table (just a table to hold your data in temporarily), or to create a linked table to the data source if possible. Then, write queries which select that data from the staging table or linked table and then append or update to to your final table as appropriate

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing updates (Access 2000)

    The updating was the real meat of my question - so you are saying there is NO way for an import to update to an existing record in a table. Can you think of any other way to accomplish the same end result?

    Continuing with my payment example - what happens is that in File1 I receive an account with a BalanceDue, but NULL for PaymentAmount and NULL for PaymentDate. Several days later, a record (always in the same "shape") for the same account will show up in File2 - now showing a value for PaymentAmount and PaymentDate. I need to capture that updated information - but I do NOT want to have two seperate records in my file (since one of the reports I generate is the number of accounts with and without payments).

    Also, the source is a file based (not database-based) application - so my only "updates" are the periodic text file extracts. In other words - unfortunately - there is no way to link directly to the source.

    Any ideas would be much appreciated.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing updates (Access 2000)

    My approach would be:
    Import the data in a temporary table.
    for each record in the temporary table, check if it exist in the destination table.
    If the record don't exist, append it
    if the record exist, compare each field and update the destination field if there are not the same.
    I would do it with code.
    Maybe this could be done with replication but i never used it and had no experience with it
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing updates (Access 2000)

    There are suggestions about ways to handle this in other posts. Imports can only *append* records to a new or existing table.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •