vba 使用 DoCmd.TransferSpreadsheet 方法从电子表格导入数据时，如何覆盖 Access 中的表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27321995/
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
How to overwrite a table in Access when importing data from a spreadsheet using the DoCmd.TransferSpreadsheet Method?
提问by
I can't figure out how to overwrite current table in Access when I import data from an Excel spreadsheet using the DoCmd.TransferSpreadsheetmethod. The code below appends the imported data to table1while I want to overwrite it with the imported data.
当我使用该DoCmd.TransferSpreadsheet方法从 Excel 电子表格导入数据时，我无法弄清楚如何覆盖 Access 中的当前表。下面的代码将导入的数据附加到，table1而我想用导入的数据覆盖它。
Sub AccImport()
Dim s As Long: s = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Dim acc As New Access.Application
acc.Visible = True
acc.OpenCurrentDatabase "F:\dbs\myDB.accdb"
acc.DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:="table1", _
Filename:=Application.ActiveWorkbook.FullName, _
HasFieldNames:=True, _
Range:="Sheet1$A1:P" & s
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
End Sub
采纳答案by HansUp
Discard the existing data from table1before calling TransferSpreadsheet:
在调用之前丢弃table1 中的现有数据TransferSpreadsheet：
acc.OpenCurrentDatabase "F:\dbs\myDB.accdb"
acc.CurrentDb.Execute "DELETE FROM table1;", 128 ' dbFailOnError = 128
acc.DoCmd.TransferSpreadsheet ...
If the structure of the target table must change to accept new data from TransferSpreadsheet, you can remove the table first and allow TransferSpreadsheetto re-create it with the new structure. You can execute DROP TABLE table1;or use the DoCmd.DeleteObjectmethod to remove the table.
如果目标表的结构必须更改以接受来自 的新数据TransferSpreadsheet，您可以先删除该表并允许TransferSpreadsheet使用新结构重新创建它。您可以执行DROP TABLE table1;或使用该DoCmd.DeleteObject方法删除表。
