Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Data from Another DB (2002)

    I am dealing with collecting data in an Access database in an unclassified environment that I use to update one on a classified system. I am currently exporting the new data to a .csv file for import to the other system. In the main data table I have created an 'update time/date' field that gets updated when a new record is added. My query to pull only the new records is based on the date I put in that is the last time I exported the data. If I forget or fail to document the last export I did, I have to research where I left off as I usually export the data every 2 weeks. Is there a better way to syncronize the tables from one database to the other such as copying the entire DB to the target system and linking them to update when I open the file? I used to be dealing with 2 or 3 new records on one table and 30-40 new records on the main data table. It is now growing to 200+ records for each 2 week update and don't want to lose any data from one to the next.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Updating Data from Another DB (2002)

    If it is just the one table then you could just run a append query copying just the records that don't appear in the target database's table. This is only good for new records of course.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Updating Data from Another DB (2002)

    If you automate the running of the query that pulls out the new records, then you could also automate the recording of the fact that you have done it. That way you don't have to remember to do it.

    A couple of possibilities.

    Add a new field "Exported" yes/no to your table. Have one query that pulls out all the records that have not been exported, then run another query that updates Exported to Yes for all records.

    Create a new table with just one field and one record "LastExportDate". The Export query would select records created after the current "lastExportDate", then after uopu run it, update the value in that table via another query.
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Updating Data from Another DB (2002)

    You've suggested one approach - linking from the classified database to the other database, and then importing all the records that don't match an existing record. That assumes that such an approach meets your security requirements. Another would be to put a simple boolean flag in the records in the unclassified database, and then when you export the data to a CSV format, you could run a query to update all of the currently unmarked records after the export was completed. The query to export the records would simply check the status of the "exported" flag to determine what records to export. This could all be run in code as well, so the process was essentially automated and that would minimize the possibility of human errors.
    Wendell

Posting Permissions

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