使用索引将Excel数据导入到Access中-如何防止弹出消息 [英] Importing Excel Data into Access Using Index - How to Prevent Popup Message

问题描述

我正在尝试使用索引将Excel中的每月数据导入到表中(通过将新记录追加到表中).

I am trying to use an Index to import monthly data from Excel into a Table (by Appending new records to the Table).

我在要发布的表中创建了一个唯一索引，以防止导入重复记录.

I have created a unique index in the Table being posted to to prevent the import of duplicate records.

但是，当我单击导入(并假设记录已经存在)时，它仍然为用户提供了选择，可以通过在以下消息上单击是"来再次导入相同的记录:

However when I click import (and assuming the records already exist) it still gives the user the option to import the same records again by clicking yes to the message below:

如果条目已经存在并且不给用户上述消息选项，我如何使宏不运行?

How would I get the macro to not run if the entries already exist and also not give the user the above message option?

我尝试使用下面提到的一些代码暂时禁止显示消息，但是无论如何它只是处理导入并将条目重新添加到表中.

I have tried suppressing messages temporarily using a bit of code as referred to below, but that just processes the import anyway and re-adds the entries to the table again.

谢谢

推荐答案

Excel导入经常会出现问题(即使您在此特定文件中未找到任何问题)，尤其是如果Excel文件是由人创建或编辑的. 我的建议是将文件导入到包含所有文本字段(F1，F2等)和标识字段的临时表中. 使用所有文本字段，所有数据都应该导入，而没有任何转换问题. 不要将第一行用作列标题.将其导入为第一行数据.这样，您可以轻松检查正确的列标题. 如果正确，则删除该行.

Excel imports often have issues (even if you haven't found any in this particular file), expecially if the Excel file is created or edited by humans. My suggestion would be to import the file into a temporary table consisting of all text fields (F1, F2, etc) and an identity field. Using all text fields, all data should import without any conversion problems. Don't use the first row as column heads. Import it as the first row of data. That way you can easily check for correct column headings. If correct, just delete that row.

然后使用查询和/或代码对临时表进行验证和清理数据，以识别不符合永久表要求的任何数据.例如.选择*从WHERE Not IsNumeric(F3).

Then validate and scrub the data using queries and/or code against the temporary table to identify any data that does not meet the requirements of your permanent tables. E.G. Select * From WHERE Not IsNumeric(F3).

然后删除所有前导或尾随空格，双精度空格等，以及您可能要执行的其他任何清理操作.

Then remove any leading or trailing spaces, double spaces, etc. and whatever other cleanup you might like to do.

然后使用带有外部联接(在PK字段上)的查询将临时表中尚未在永久表中的行追加到永久表中.

Then use a query with an outer join (on your PK fields) to append rows from the temp table, that are not already in the permanent table, to the permanent table.

这篇关于使用索引将Excel数据导入到Access中-如何防止弹出消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！