开发者

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:

  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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜