3265 error "Item not found in collection"

[AndyB2]

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

Private Sub SummaryButton_Click()
Dim CurrQuality As Long
Dim CurrHour As Date
Dim CurrProd As Long
Dim CurrEmployee As Long
Dim MyDb As Database, MDrs As Recordset, MDSrs As Recordset2


Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MDrs = MyDb.OpenRecordset("MachineData", DB_OPEN_TABLE)
Set MDSrs = MyDb.OpenRecordset("MachineData Summary", DB_OPEN_DYNASET)

'Clear old summary file
MDSrs.MoveFirst
While Not MDSrs.EOF
    MDSrs.Delete
    MDSrs.MoveNext
Wend


MDrs.MoveFirst  'start at the first record
CurrQuality = MDrs(Quality)
CurrHour = MDrs(TimeStamp1)
CurrProd = MDrs(Production)
CurrEmployee = MDrs(Employee#)

While Not MDrs.EOF
    CurrHour = Minute(MDrs(TimeStamp1))
    CurrQuality = MDrs(Quality)
        While CurrQuality = MDrs(Quality) And CurrHour = Minute(MDrs(TimeStamp1))
            MDrs.MoveNext
        Wend
    MDrs.MovePrevious
    MDSrs(StartProduction) = CurrProd
    MDSrs(Machine) = MDrs(Machine)
    MDSrs(StartTimeStamp1) = CurrHour
    MDSrs(Quality) = CurrQuality
    MDSrs(Employee#) = MDrs(Employee#)
    MDSrs(EndTimeStamp1) = MDrs(TimeStamp1)
    MDSrs(EndProduction) = MDrs(Production)
    MDrs.MoveNext
Wend

End Sub

Error is at "CurrQualit y = MDrs(Quality)" And if anyone knows of a command to clear a table vs what I'm doing, deleting one record at a time it would be appreciated.

[Stewart Ross]

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:

CurrQuality = MDrs("Quality")

Secondly, if you wish to clear all records from a table there is no need to use a recordset at all. You could simply do something like:

CurrentDb.Execute "Delete * from [YourTable] Where [SomeCondition]=[WhateverIsBeingTested]"

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.

-Stewart

[AndyB2]

“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.

[Stewart Ross]

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

[AndyB2]

I’m 95% sure I understand what you are saying. Do all the data crunching on the SQL server. My original plan was to copy the viewed data for that machine locally then crunch the data, at most 12,000 records a day given a 10 hour work day per machine.

The one thing I have a hard time getting my head around your suggestion is an end user needs to be able to bring up data anytime during the day. Are you going to tell me (show me) how to pass a request from Access to MS SQL 2008 to run a SQL program to fetch the data requested? If that is what you are suggesting it will have to be multiuser friendly.

I have written some SQL queries in Oracle, far from a pro.

Do I understand your suggestion?

[Stewart Ross]

You are right about what I'm suggesting. Use SQL Server for what it is good at - including the back-end processing of SQL queries to do the number crunching. Access, which can present the user with a friendly forms-based front-end, would use the queries you develop as the base on which everything else is built.

Users would not be interacting directly with queries as such; for ease of use you'd create an Access form or report based on such a query to lay out the data in whatever is the most appropriate way.

Make use of Access's strengths as a system for creating user-friendly forms and reports based on simple switchboards (see, for example, the Northwind sample database supplied with Access for ideas on how users can interact via forms and reports). I am also suggesting that you make use of SQL-Server's inherent processing power to perform the back-end number crunching - it makes much more sense than spending what will be a lot of time inventing in VBA what SQL can do already.

I and other posters could probably come up with SQL queries to help start you on the road to developing your application, at least short-cutting the effort you would undoubtedly be putting in if you try to develop everything in VBA.

You may need to adopt hybrid approaches, given the scale of the processing involved (for example, creating temporary tables to store intermediate results each hour to speed up user querying), but this is not something at present that can be predicted without prototyping up a working system - which is what you have already started on with your test system data.

-Stewart

[AndyB2]

Know a good referance book for MicroSoft SQL Server2008 R2 so I don't have to bother anyone for every and, if, or, but.

Sounds like I'm going to spend some time in that.

[NeoPa]

This may sound like a trite answer, but when I was working in T-SQL (SQL Server's SQL language) I found everything I needed on the web. I kept a page open at Transact-SQL Reference (2008 R2) and from there I could find everything I ever needed.

Otherwise, we have a pretty responsive SQL Server forum here too :-)

[AndyB2]

Ross,

Just getting back with you, here is what Rabbit helped me out with for an SQL to do the same thing I was doing above:

SELECT U1.Quality, U1.TimeStamp1 AS StartTime,
    CASE WHEN U2.PriorTimeStamp1 IS NULL THEN (
        SELECT MAX(TimeStamp1) FROM @t
    ) ELSE U2.PriorTimeStamp1 END AS EndTime 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY T1.TimeStamp1) AS S,
        T1.Quality, T1.TimeStamp1, T2.TimeStamp1 AS PriorTimeStamp1
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp1) AS R, * 
        FROM @t
    ) AS T1
    LEFT JOIN (
        SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp1) AS R, * 
        FROM @t
    ) AS T2
    ON (T1.R - 1) = T2.R
    WHERE T1.Quality != T2.Quality
        OR T2.Quality IS NULL
        OR DATEPART(MINUTE, T1.TimeStamp1) != DATEPART(MINUTE, T2.TimeStamp1)
) AS U1
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY T1.TimeStamp1) AS S,
        T1.Quality, T1.TimeStamp1, T2.TimeStamp1 AS PriorTimeStamp1
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp1) AS R, * 
        FROM @t
    ) AS T1
    LEFT JOIN (
        SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp1) AS R, * 
        FROM @t
    ) AS T2
    ON (T1.R - 1) = T2.R
    WHERE T1.Quality != T2.Quality
        OR T2.Quality IS NULL
        OR DATEPART(MINUTE, T1.TimeStamp1) != DATEPART(MINUTE, T2.TimeStamp1)
) AS U2
ON (U1.S + 1) = U2.S

I have not put a stop watch it, but this code appears slower than what I created above. I suspect is because of the way the data must be retrieved, row by row, we are losing SQL's power.

Here is Rabbit's quick overview:

"There's a lot going on in that SQL, let me know if you have any questions.

Here's a quick rundown. It joins the table to itself on the row to the previous row. Which can be used to see if the previous row's information differs from the current row's information.

It then takes that and joins to itself again to get the start and end data."

I'm debating if doing the crunching in SQL is the way to go?

One other factor I didn't mention before is we have two networks at our plant:

1) Company wide network
2) Engineering network (only in our plant)

Both networks are attached to one computer, this computer is where I am planning on doing the crunching before passing the data into the company wide network. It is just a matter of doing the crunching in Access or SQL?
If you still feel strongly that I should stay in SQL I’ll still look at going down this path.

[Rabbit]

The convoluted SQL that I posted is a result of having to work around the drawbacks of SQL. The problem is that while it is record based, it is not record agnostic, by which I mean a record is unaware of the other records. And as a result, when you need to compare between records, it can be a performance hog. If you put in the correct indexes, you may be able to speed up the process considerably.

[NeoPa]

Rabbit:
it is not record agnostic

Do you not mean it is record agnostic? I know this seems fussy but I was confused, and I prefer to understand where I can.

PS. This is a very important point of course, that people often struggle to appreciate when dealing with data in an RDBMS.

[Rabbit]

Sorry, yes, I meant it is record agnostic.