3265错误“在集合中找不到项目” [英] 3265 error "Item not found in collection"

问题描述

VBA for Access尝试从一个表读取并将摘要数据写入下一个表。

VBA for Access attempting to read from one table and write summary data to the next.

< span class =codeLinkonclick =Blur（this，this.parentNode.parentNode，getChildren（this），true）;>展开 | 选择 | Wrap | 行号

推荐答案

首先，如果您尝试从第22行开始访问记录集的字段，则必须将字段名称括在双引号中，如下所示：

Firstly, if you are trying to access a recordset''s fields as you are doing from lines 22 onwards you have to enclose the field name in double quotes,like this:

展开 | 选择 | Wrap | 行号

如果你能够更清楚地解释你想要做什么，我相信我们可以进一步帮助你了解细节。

您问：

大图：

我们正在尝试在每台机器上使用OPC连接来跟踪工厂车间的生产情况。我们有一个数据记录器来提供MS SQL 2008 R2数据库，然后我尝试使用MS / Access作为最终用户的前端。

OPC将每隔3秒收集一次数据（看起来有点过分，但我们不希望操作员等待传输停机代码）。我们在场上有大约120台机器，因此我计划在每台机器上使用SQL 2008中的VIEW来限制网络流量，每台机器每小时1200条记录，而不带视图的每小时144,000条记录。更多编码，但我认为它应该更快。如果我错了，请告诉我。

我们对每台机器的生产，效率和停机时间（即过程监控）的按小时快照感兴趣。上面的代码是我第一次尝试在小时结束或发现停机代码发生变化时将数据汇总到一行数据。计划是使用摘要数据来为最终用户提供报告。

每晚一次，类似的代码将在MS SQL数据库上运行，以将汇总数据附加到历史文件中一天的数据。详细数据仅保留当天。

我们希望向每台机器提供反馈，让操作员知道他们是否正在计划运行。并在单元格的末尾有一个屏幕，显示单元格中的每台机器如何实时进行。听起来像大哥一样，但我们确实试着专注于这个过程而不是大多数时间的操作员。

我知道一点编程，建议总是赞赏。但不知道VBA的语法让我发疯。


PS。我现在正在处理的数据是来自数据记录软件的虚拟数据。

“If you can come up with a clearer explanation of what you are trying to do I''m sure we can assist you further with specifics.”
You asked:
Big Picture:
We are attempting to use an OPC connection on each machine to track production on the factory floor. We have a data logger to feed the MS SQL 2008 R2 database, and then I’m trying to use MS/Access as the front end for the end user.
OPC will be collecting data about every 3 seconds (seems like overkill, but we do not want to operator waiting around for a downtime code to be transmitted). We have about 120 machines on the floor so I’m planning on using a VIEW in SQL 2008 for each machine to limit the network traffic, 1200 records per hour per machine vs. 144,000 per hour quarrying without the view. More coding, but I would think it should be faster. Please tell me if I’m wrong.
We are interested in a “by hour snapshot” of each machine’s production, efficiency and downtime, i.e. Process monitor. In the code above is my first attempt at summarizing the data down to one line of data when the hour is up or a change in the downtime code has been found. The plan is to use the summary data to drive the reports for the end user.
Once a night, similar code will be ran on the MS SQL database to append the summarized data to the history file for that day’s data. Detail data will only be kept for the current day.
Down the road we want feedback to each machine letting the operator know if they are running to plan or not. And have a screen at the end of the cell showing how each machine in the cell is doing in real time. Sounds like big brother watching, but we do try to focus on the process and not the operator most of the time.
I know a little programing, suggestions always appreciated. But not knowing the “grammar” of VBA is driving me crazy.

PS. Data I''m dealing with now is dummy data from the data logging software.

根据您上面概述的内容，我确信使用以下方法计算汇总机器性能数据是有益的。 SQL查询而不是基于记录集的处理，特别是如果这些查询被传递给SQL-Server本身进行处理（访问术语这些''传递''查询，因为查询不是由Access处理，而是传递给主机数据库后端服务器进行处理）。


由于多种原因，SQL查询的运行速度可能比使用Access中的记录集快得多。数据库引擎以记录集处理不可能的方式进行优化。如果使用SQL-Server的处理，您可以比Access更快地处理后端服务器上的记录，特别是当Access必须从SQL Server获取其所有数据以对其执行任何操作时， SQL Server从Access运行的传递查询只是将结果发送回Access，而不是处理的记录。


我们需要帮助的是指示名称所涉及的表格和字段，以及您希望从小时快照和每日摘要运行中得出的摘要数据。


使用SQL的一个好处是SQL专注于什么的处理，而不是如何;使用记录集处理你必须自己解决 how 部分，以及努力使用VBA语法来获得第一个基础。与在SQL中准备摘要查询相比，这是一种非常容易出错且耗时的方法 - 或者，如果您不熟悉SQL本身，则在Access中的图形查询设计器中开始使用。

-Stewart

From what you have outlined above I am sure that it would be beneficial to compute the summary machine performance data using SQL queries instead of recordset-based processing, particularly if those queries were passed to SQL-Server itself for processing (Access terms these ''pass-through'' queries, as the query is not handled by Access but instead passed through to the host database back-end server for processing).

SQL queries are likely to run much faster than using recordsets in Access, for a number of reasons. The database engine is optimised in ways that recordset processing cannot be. If SQL-Server''s processing is used you have the ability to process records on the back-end server much faster than Access could do so, particularly as Access has to fetch all its data from SQL Server to do anything with it, whereas pass-through queries run by SQL Server from Access simply send the results back to Access, not the records processed.

What we''d need to help you is an indication of the names of the tables and fields involved, and the summary data you expect to result from the ''by hour''snapshot and the daily summary run.

A positive advantage of using SQL is that SQL focuses on the what of the processing, not the how; with recordset processing you have to work out for yourself the how part, as well as grappling with VBA syntax to get to first base. This is a very error-prone and time-consuming approach compared to preparing a summary query in SQL - or, if you are not familiar with SQL itself, in the graphical query designer in Access to begin with.

-Stewart

这篇关于3265错误“在集合中找不到项目”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！