从Excel导入两列到现有Access2010表中，使用transferspreadsheet会出错 [英] Import two columns from Excel into existing Access2010 table gives error using transferspreadsheet
问题描述
我有一个具有12列的excel电子表格，我只需要根据最后两列导入数据(其余的则需要填写最后一列.)
I have an excel spreadsheet, with 12 columns, where I only need to import data based on the last two (The rest are needed to fill out the last column.)
如图所示，Access中有一个名为"ProjektDelledning"的表，其中已经存在两列DelledningID和SaneringsmetKode.如果已经存在DelledningID，则需要更新SaneringsmetKode；如果表中不存在，则需要添加该值.因此，导入后，Dellerned 258的Acces中的SaneringsmetKode = 1，依此类推.
As shown on the picture I have a table in Access called "ProjektDelledning" where the two columns DelledningID and SaneringsmetKode are already present. If a DelledningID is already present it needs to update the SaneringsmetKode, if it isn't present in the table, it needs to add the value. So Delledning 258 should have a SaneringsmetKode = 1 in Acces after import and so forth.
到目前为止，我已经尝试使用此功能:
So far I've tried using this:
Public Function Import2Columns()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ProjektDelledning", "C:\Users\JGJ\Desktop\Sanering.xlsx", True, "Concatenate!L:M"
End Function
我收到一条错误消息，说Access无法将所有数据附加到表中.由于键冲突，删除了0个reocrd中的字段内容，并且丢失了0条记录...
I get an error saying that Access was unable to append all the data to the table. The Contents of field in 0 reocrd(s) were deleted, and 0 record(s) were lost due to key violations...
值得注意的是，在Access表中，ProjektID和DelledningID都是键.
It is worth noting that in the Access table, both ProjektID and DelledningID is a key.
有关如何使用transferspreadsheet或其他方法进行正确导入的任何帮助.我想使用宏使导入工作.
Any help as to how I can get a correct import using transferspreadsheet or a different method works. I would like to make the import work using a Macro.
推荐答案
一种方法可能是导入到临时表(或只是 link 范围).
A method could be to import to a temp table (or just link the range).
然后，使用它运行组合的更新/追加查询.
Then, using this, run a combined update/append query.
智能访问的旧提示是我的最爱之一:
This old tip from Smart Access is one of my favourites:
一次查询更新和追加记录
Update and Append Records with One Query
艾伦·比格斯(Alan Biggs)
By Alan Biggs
您是否知道可以在访问两者"中使用更新查询 同时更新和添加记录?如果您有的话，这很有用 表格的两个版本，tblOld和tblNew，并且您想要集成 从tblNew到tblOld的变化.
Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.
请按照以下步骤操作:
-
创建一个更新查询并添加两个表.通过将tblNew的关键字段拖到tblOld的匹配字段来连接两个表.
Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.
双击关系，然后选择联接选项，该联接选项包括tblNew中的所有记录，并且仅包括与tblNew中的记录匹配的记录. tblOld.
Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.
从tblOld中选择所有字段并将其拖到QBE网格上.
Select all the fields from tblOld and drag them onto the QBE grid.
对于每个字段，在tblNew.FieldName中的更新到"单元格中，其中FieldName与tblOld的字段名称匹配.
For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.
从视图"菜单中选择查询属性"，然后将唯一记录"更改为False. (这会关闭SQL中的DISTINCTROW选项 看法.如果不选择此项，您的记录中只会有一个空白记录 结果，但您希望为每个新记录添加一个空白记录 到tblOld.)
Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)
运行查询，您将看到对tblNew的更改现在位于tblOld中.
Run the query and you'll see the changes to tblNew are now in tblOld.
这只会将已添加到tblNew的记录添加到tblOld. tblNew中不存在的tblOld中的记录仍将保留在 tblOld.
This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.
这篇关于从Excel导入两列到现有Access2010表中，使用transferspreadsheet会出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
