Docmd.TransferText to update data

Question

i am using Docmd.TransferText to import data from a text file into my access table.

i would like it to do the following:

1. if the record already exists, then update it
2. if the record does not exist then add it

how do i accomplish this?

currently i have this line:

DoCmd.TransferText acImportDelim, yesyes, "table3", "C:\requisition_data_dump.txt", True

Answer 1

You cannot do this with an import. You could use transfertext to link the data as a table and then run an update and an append query.

sSQL="UPDATE table3 INNER JOIN MyLinkedTable " _
    & "ON table3.ID=MyLinkedTable.ID " _
    & "SET table3.SomeField=MyLinkedTable.SomeField "
CurrentDB.Execute sSQL, dbFailOnError

sSQL="INSERT INTO table3 (ID,SomeField ) " _
    ="SELECT ID, SomeField FROM MyLinkedTable " _
    & "LEFT JOIN table3 " _
    & "ON table3.ID=MyLinkedTable.ID " _
    & "WHERE table3.ID Is Null "
CurrentDB.Execute sSQL, dbFailOnError