vba Microsoft Access DoCmd.TransferText 附加列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7300167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Microsoft Access DoCmd.TransferText additional column
提问by kevin
I'm using DoCmd.TransferTextto import data from .cvs file. I need add another column with current date to imported information. Is there any simple way to do this?
我正在使用DoCmd.TransferText从 .cvs 文件导入数据。我需要将具有当前日期的另一列添加到导入的信息中。有没有简单的方法可以做到这一点？
回答by Banjoe
There's a few approaches you can use:
您可以使用以下几种方法：
-Parse the file and create INSERT statements via VBA instead of using TransferText.
- 解析文件并通过 VBA 而不是使用 TransferText 创建 INSERT 语句。
-If the table already exists, you're appending data to an existing table, and you have an auto-number ID field or some other way to distinguish new data from old data then you can add the current date after importing via an UPDATE statement:
- 如果表已经存在，您将数据附加到现有表，并且您有一个自动编号 ID 字段或其他一些方法来区分新数据和旧数据，那么您可以在通过 UPDATE 语句导入后添加当前日期：
Dim latestID As Long
latestID = DMax("yourIDField", "yourTable")
DoCmd.TransferText acImportDelim, , "yourTable", "c:\import.csv", True
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE yourTable SET importedDate = #" & Date & "# WHERE yourIDField > " & latestID
DoCmd.SetWarnings True
-If a new table is being created each time you import, you can use an ALTER statement to add the column then an UPDATE to enter the current date:
- 如果每次导入时都会创建一个新表，则可以使用 ALTER 语句添加列，然后使用 UPDATE 输入当前日期：
DoCmd.TransferText acImportDelim, , "yourTable", "c:\import.csv"
DoCmd.SetWarnings False
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN importDate DATE"
DoCmd.RunSQL "UPDATE yourTable SET importDate = #" & Date & "#"
DoCmd.SetWarnings True
回答by Arup Banerjee
You can use schema.ini instead transfer text method.
您可以使用 schema.ini 代替传输文本方法。
Check the following blog it describes more about schema.ini https://officeaccelerators.wordpress.com/2015/02/08/exporting-text-file-from-access/
检查以下博客，它描述了更多关于 schema.ini https://officeaccelerators.wordpress.com/2015/02/08/exporting-text-file-from-access/
Set cn = CreateObject("ADODB.Connection")
Set cn = CurrentProject.Connection
sSQL = "insert into TanleName(Field1,Field2,Date) select tab1.Field1,tab1.Field2,date() from "
sSQL = sSQL & "[text;database=FilePath\;HDR=Yes].[Test.csv] as Tab1"
cn.Execute sSQL
回答by user928084
Another way is to create a linked table for the .csv file (select the correct file type from the Open dialog popup form when creating a new linked table) and create an append query with the additional field holding Date()or Now()as default value.
另一种方法是为 .csv 文件创建链接表（在创建新链接表时从“打开”对话框弹出表单中选择正确的文件类型）并使用附加字段Date()或Now()作为默认值创建附加查询。
