0

While I think I may be able to work out a hack job solution solution, I'm hoping to identify the most simple way to regularly update a MS Access table to exactly match the contents of a linked .TXT file. I want MS Access to change values in a row in the table if the values in the same row in the linked .TXT file have changed, as well as add any new rows and delete any rows that are no longer in the .TXT file.

I presently have an append query to add the new rows. Is it possible to do all of the above in one query, or do I have to run both an append and (one or two?) update query/queries? Any/all advice is welcome. Cheers.

AmandaNicole
  • 33
  • 8
  • The text file data is cumulative? Could maybe just delete all the records in the table and reimport all records again, as long as an autonumber field is not designated as PK and dependent records reference it as FK. Are there dependent tables? – June7 Jul 21 '17 at 03:06

1 Answers1

0

You can link (attach) the text file. Then run a combined append/update query:

Update and Append Records with One Query

When a new text file arrives, just replace the linked text file with the new file.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • This is exactly what I'm looking for - thanks Gustav. I have a linked .txt file, and it is automatically updated for me. I wanted a query that would basically make the data table match the latest .txt file. Could I also use the same query to delete records from the data table that no longer exist in the .txt file? – AmandaNicole Jul 22 '17 at 04:07
  • No, it will only append or update. If you also need deleting, you are effectively asking for a replacement of the entire table. If so, you perhaps could link the text file and simply replace it when updated. – Gustav Jul 22 '17 at 07:35