Docmd.TransferText更新数据 [英] Docmd.TransferText to update data

问题描述

我使用 Docmd.TransferText 来从一个文本文件中的数据导入到我的访问表。

我想它要做到以下几点：

1. 如果记录已经存在，然后更新它
2. 如果记录不存在，然后将其添加

我如何做到这一点？

目前，我有这样一行：

  DoCmd.TransferText acImportDelim，yesyes，表3，C：\ requisition_data_dump.txt，真
 

解决方案

您不能导入做到这一点。你可以使用transfertext数据链路作为一个表，然后运行更新，并追加查询。

  sSQL =UPDATE表3 INNER JOIN MyLinkedTable_
    ＆放大器; 开table3.ID = MyLinkedTable.ID_
    ＆放大器; SET table3.SomeField = MyLinkedTable.SomeField
CurrentDB.Execute sSQL，dbFailOnError

sSQL =INSERT INTO表3（ID，SomeField）_
    =SELECT ID，SomeField从MyLinkedTable_
    ＆放大器; LEFT JOIN表3_
    ＆放大器; 开table3.ID = MyLinkedTable.ID_
    ＆放大器; WHE​​RE table3.ID IS NULL
CurrentDB.Execute sSQL，dbFailOnError
 

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

解决方案

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

这篇关于Docmd.TransferText更新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！