VBA代码确保刚从Excel发送到Access的数据不重复 [英] VBA Code to ensure data just sent to Access from Excel are not duplicates

问题描述

嗨大家好 - 我需要帮助请

我正在尝试将来自Excel的数据去重复访问

我遇到问题的部分是

（我已在底部附上完整代码以帮助）

任何帮助都将非常感谢和需要

Hi folks - I am in need of help please
I am trying to de-duplicate data coming from Excel to Access
The part I am having problems with is
(I have attached the full code at the bottom too to help)
Any help would be greatly appreciated and needed

展开 | 选择 | Wrap | 行号

推荐答案

好奇为什么用户必须点击忽略重复项。


我会完全将它们排除在要检查的数据集之外，或者忽略它们将它们红色并发出一个INSERT，它将跳过指定唯一键字段的dupes。


您打算使用此代码的方式是什么？


Nic; o）

Just curious why the user has to click for ignoring duplicates.

I would have excluded them entirely from the data set to be inspected, or ignored them and issued an INSERT that will skip dupes for the specified unique key field(s).

What''s the way you intend this code to work?

Nic;o)

嗨Nico感谢您加入我的主题我真的可以帮助您。我需要的是确保数据是通过命令按钮导入的，如果多按一次数据不会复制多次按下按钮的次数。因此，假设您按下该按钮可将指标导入访问权限。但是另一位同事没有检查访问权以查看数据是否存在，他们按下按钮再次导入数据。我需要阻止这种情况发生。请告知

Hi Nico thanks for getting to my thread I could really use your help. All I need is to make sure the data being imported via a command button if pressed more then once that the data does not duplicate as many as extra times the button was pressed. So lets say you press that button to import metrics into access. But another co-worker did not check access to see if the data is there, and they press the button to import the data again. I need to block this from happening. Please advise

如图所示，当插入新记录时，Access将阻止当你定义了唯一的ID时重复。


我看到你的添加代码：

RST！Date = MDate

RST！ metric_ID = Mmetric_ID

RST！Value = Advocatecomp

RST！Status =" Active"


我通常会发出Docmd.execute（"< Insert statement>）"添加行，这将在添加重复行时抑制错误消息，因为它不会添加数据。


仍需要关于状态的一些信息。领域。如果有一行Status<> 激活，这会触发状态字段更改吗？


Nic; o）

As indicated, when inserting a new record, Access will "block" duplicates when you have defined the unique ID.

I see your add code:
RST!Date = MDate
RST!metric_ID = Mmetric_ID
RST!Value = Advocatecomp
RST!Status = "Active"
and I assume MDate, Mmetric_ID and Advocatecomp make up the unique record. Thus defining a unique index on the table consisting from these fields with the option "No duplicates allowed" will make sure no duplicate records can be added.

I normally issue a "Docmd.execute ("<Insert statement>")" to add rows and this will suppress error messages when adding a duplicate row, as it won''t add the data.

Still would need some information on the "Status" field. When there''s a row with Status <> "Active", will this trigger the Status field to change ?

Nic;o)

这篇关于VBA代码确保刚从Excel发送到Access的数据不重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！