Docmd.TransferText to update data
i am using Docmd.TransferText
to import data from a text file into开发者_如何学JAVA my access table.
i would like it to do the following:
- if the record already exists, then update it
- 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
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
精彩评论