Properly search mutlifield indexes to add/edit values

[thechazm]

Hey guys,

If I have a table with four fields in a single index that I called "Test01" in vba would I still search for the regular named fields and it automatically pickup on the index or do I need to call that index somehow to properly search the index?

Sounds silly but any help is greatly appreciated.

Thanks,

TheChazm

 

[thechazm]

Actually I would like to add the table specific's so maybe someone might be able to see something I don't.

[Compiled Metrics] - Is the Name of the table
[Comp ID] = Autonumber
[Daily Date] = Date/Time (Just storing short date value's here)
[Shop ID] = Number
[Project ID] = Number
[STR Personnel] = Number
[STR Time] = Number
[STR Daily MPD] = Number
[CTR Personnel] = Number
[CTR Charged Time] = Number
[CTR Daily MPD] = Number
[SL Personnel] = Number
[SL Time] = Number
[SL Daily MPD] = Number
[NUC] = Yes/No

The indexes are on the Primary key which is [Comp ID] and I have added an index called Test01 which includes [Daily Date], [Shop ID], [Project ID], and [Nuc]. With over 50K records it seems to be slowing down pretty fast taking almost 4 hours to update.

That's why I was asking the question earlier about my search meathod because I search for all four fields in my search included in the Test01 Index.

Any help or inputs is greatly appreciated.

Also here is my function that searches and modifies the records.

Function CompileChargeMetrics()
Dim db As Database, rs As DAO.Recordset, rs2 As DAO.Recordset, ii As Long, CountNum As Long
Set db = CurrentDb
'--------------------------------------------------------------------------Start CL Metrics-------------------------------------------------------------------------------------
Set rs = db.OpenRecordset("Select * from [(Compute) CL Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & _
        rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "CL Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("IBB Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![CL Personnel] = rs("Personnel")
            ![CL Charged Time] = rs("Charge Total")
            ![CL Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![CL Personnel] = rs("Personnel")
            ![CL Charged Time] = rs("Charge Total")
            ![CL Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
    rs.MoveNext
Loop
rs.Close
rs2.Close
 
'--------------------------------------------------------------------------End CL Metrics-------------------------------------------------------------------------------------
 
 
'--------------------------------------------------------------------------Start CTR Metrics-------------------------------------------------------------------------------------
 
Set rs = db.OpenRecordset("Select * from [(Compute) CTR Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
 
    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "CTR Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("IBB Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![CTR Personnel] = rs("Personnel")
            ![CTR Charged Time] = rs("Charge Total")
            ![CTR Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![CTR Personnel] = rs("Personnel")
            ![CTR Charged Time] = rs("Charge Total")
            ![CTR Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
    rs.MoveNext
Loop
rs2.Close
rs.Close
'--------------------------------------------------------------------------End CTR Metrics-------------------------------------------------------------------------------------
 
'--------------------------------------------------------------------------Start OV Metrics-------------------------------------------------------------------------------------
 
 
Set rs = db.OpenRecordset("Select * from [(Compute) OV Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "OV Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("IBB Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![OV Personnel] = rs("Personnel")
            ![OV Charged Time] = rs("Charge Total")
            ![OV Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![OV Personnel] = rs("Personnel")
            ![OV Charged Time] = rs("Charge Total")
            ![OV Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
 
    rs.MoveNext
Loop
rs2.Close
rs.Close
 
'--------------------------------------------------------------------------End OV Metrics-------------------------------------------------------------------------------------
 
 
 
'Set rs = db.OpenRecordset("Select * from [(Compute) OV Metrics]", dbOpenSnapshot)
'Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
'
'rs.MoveLast
'rs.MoveFirst
'ii = rs.RecordCount
'
'Do While rs.EOF = False
'    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
'    StatusLabel "OV Metrics: " & rs.AbsolutePosition & " - " & ii
'    If rs2.NoMatch = True Then
'        With rs2
'            .AddNew
'            ![Daily Date] = rs("IBB Date")
'            ![Shop ID] = rs("Home Shop ID")
'            ![Project ID] = rs("Project ID")
'            ![Nuc] = rs("Nuc")
'            ![OV Personnel] = rs("Personnel")
'            ![OV Charged Time] = rs("Charge Total")
'            ![OV Daily MPD] = rs("Daily MPD")
'            .Update
'        End With
'    Else
'        With rs2
'            .Edit
'            ![OV Personnel] = rs("Personnel")
'            ![OV Charged Time] = rs("Charge Total")
'            ![OV Daily MPD] = rs("Daily MPD")
'            .Update
'        End With
'    End If
'
'    rs.MoveNext
'Loop
'
'rs2.Close
'rs.Close
 
'--------------------------------------------------------------------------Start ST Metrics-------------------------------------------------------------------------------------
 
db.Execute "Delete * from [(Temp) ST Metrics]"
Set rs = db.OpenRecordset("Select * from [(Compute) ST Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [(Temp) ST Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
CountNum = 1
Do While rs.EOF = False
    StatusLabel "ST PreCompiled Metrics: " & rs.AbsolutePosition & " - " & ii
    With rs2
        .AddNew
        ![ID] = CountNum
        ![IBB Date] = rs("IBB Date")
        ![Home Shop ID] = rs("Home Shop ID")
        ![Project ID] = rs("Project ID")
        ![Nuc] = rs("Nuc")
        ![Personnel] = rs("Personnel")
        ![Charge Total] = rs("Charge Total")
        ![Daily MPD] = rs("Daily MPD")
        .Update
    End With
    rs.MoveNext
    CountNum = CountNum + 1
Loop
 
rs2.Close
rs.Close
Set rs = db.OpenRecordset("Select * from [(Compute) ST Metrics PID]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [(Temp) ST Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[IBB Date] = #" & rs("IBB Date") & "# AND [Home Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("PID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "ST Caught PreCompiled Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![ID] = CountNum
            ![IBB Date] = rs("IBB Date")
            ![Home Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("PID")
            ![Nuc] = rs("Nuc")
            ![Personnel] = rs("Personnel")
            ![Charge Total] = rs("Charge Total")
            ![Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![Personnel] = Nz(rs2("Personnel"), 0) + Nz(rs("Personnel"), 0)
            ![Charge Total] = Nz(rs2("Charge Total"), 0) + Nz(rs("Charge Total"), 0)
            ![Daily MPD] = Nz(rs2("Daily MPD"), 0) + Nz(rs("Daily MPD"), 0)
            .Update
        End With
    End If
    rs.MoveNext
    CountNum = CountNum + 1
Loop
 
rs2.Close
rs.Close
Set rs = db.OpenRecordset("Select * from [(Temp) ST Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "ST Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("IBB Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![ST Personnel] = rs("Personnel")
            ![ST Charged Time] = rs("Charge Total")
            ![ST Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![ST Personnel] = rs("Personnel")
            ![ST Charged Time] = rs("Charge Total")
            ![ST Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
    rs.MoveNext
Loop
 
rs2.Close
rs.Close
'--------------------------------------------------------------------------End ST Metrics-------------------------------------------------------------------------------------
 
'--------------------------------------------------------------------------Start OT Metrics-------------------------------------------------------------------------------------
db.Execute "Delete * from [(Temp) OT Metrics]"
Set rs = db.OpenRecordset("Select * from [(Compute) OT Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [(Temp) OT Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
CountNum = 1
Do While rs.EOF = False
    StatusLabel "OT PreCompiled Metrics: " & rs.AbsolutePosition & " - " & ii
    With rs2
        .AddNew
        ![ID] = CountNum
        ![IBB Date] = rs("IBB Date")
        ![Home Shop ID] = rs("Home Shop ID")
        ![Project ID] = rs("Project ID")
        ![Nuc] = rs("Nuc")
        ![Personnel] = rs("Personnel")
        ![Charge Total] = rs("Charge Total")
        ![Daily MPD] = rs("Daily MPD")
        .Update
    End With
    rs.MoveNext
    CountNum = CountNum + 1
Loop
rs2.Close
rs.Close
Set rs = db.OpenRecordset("Select * from [(Compute) OT Metrics PID]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [(Temp) OT Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[IBB Date] = #" & rs("IBB Date") & "# AND [Home Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("PID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "OT Caught PreCompiled Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![ID] = CountNum
            ![IBB Date] = rs("IBB Date")
            ![Home Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("PID")
            ![Nuc] = rs("Nuc")
            ![Personnel] = rs("Personnel")
            ![Charge Total] = rs("Charge Total")
            ![Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![Personnel] = Nz(rs2("Personnel"), 0) + Nz(rs("Personnel"), 0)
            ![Charge Total] = Nz(rs2("Charge Total"), 0) + Nz(rs("Charge Total"), 0)
            ![Daily MPD] = Nz(rs2("Daily MPD"), 0) + Nz(rs("Daily MPD"), 0)
            .Update
        End With
    End If
    rs.MoveNext
    CountNum = CountNum + 1
Loop
rs2.Close
rs.Close
Set rs = db.OpenRecordset("Select * from [(Temp) OT Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "OT Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("IBB Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![OT Personnel] = rs("Personnel")
            ![OT Charged Time] = rs("Charge Total")
            ![OT Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![OT Personnel] = rs("Personnel")
            ![OT Charged Time] = rs("Charge Total")
            ![OT Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
    rs.MoveNext
Loop
 
rs2.Close
rs.Close
 
'--------------------------------------------------------------------------End OT Metrics-------------------------------------------------------------------------------------
 
'--------------------------------------------------------------------------Start OV Metrics-------------------------------------------------------------------------------------
 
Set rs = db.OpenRecordset("Select * from [(Compute) OV Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Daily Date] = #" & rs("IBB Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "OV Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("IBB Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![OV Personnel] = rs("Personnel")
            ![OV Charged Time] = rs("Charge Total")
            ![OV Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![OV Personnel] = rs("Personnel")
            ![OV Charged Time] = rs("Charge Total")
            ![OV Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
 
    rs.MoveNext
Loop
rs2.Close
rs.Close
 
'--------------------------------------------------------------------------End OV Metrics-------------------------------------------------------------------------------------
'--------------------------------------------------------------------------Start STR Metrics-------------------------------------------------------------------------------------
 
BuildSTRMetrics
Set rs = db.OpenRecordset("Select * from [(Compute) STR Metrics Final]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    rs2.FindFirst "[Daily Date] = #" & rs("Daily Date") & "# AND [Shop ID] = " & rs("Home Shop ID") & " AND [Project ID] = " & rs("Project ID") & " AND [Nuc] = " & rs("Nuc")
    StatusLabel "STR Metrics: " & rs.AbsolutePosition & " - " & ii
    If rs2.NoMatch = True Then
        With rs2
            .AddNew
            ![Daily Date] = rs("Daily Date")
            ![Shop ID] = rs("Home Shop ID")
            ![Project ID] = rs("Project ID")
            ![Nuc] = rs("Nuc")
            ![STR Personnel] = rs("Personnel")
            ![STR Time] = rs("Charge Total")
            ![STR Daily MPD] = rs("Daily MPD")
            .Update
        End With
    Else
        With rs2
            .Edit
            ![STR Personnel] = rs("Personnel")
            ![STR Time] = rs("Charge Total")
            ![STR Daily MPD] = rs("Daily MPD")
            .Update
        End With
    End If
 
    rs.MoveNext
Loop
rs2.Close
rs.Close
 
'--------------------------------------------------------------------------End STR Metrics-------------------------------------------------------------------------------------
 
 
'--------------------------------------------------------------------------Start SS Metrics-------------------------------------------------------------------------------------
Set rs = db.OpenRecordset("Select * from [(Temp) SS2]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [(Temp) SS]", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
        rs2.FindFirst "[DefDate] = #" & rs("DefDate") & "# AND [Shop] = """ & rs("Shop") & """ AND [Project] = """ & rs("Project") & """ AND [Nuc] = " & rs("Nuc")
        StatusLabel "SS Metrics: " & rs.AbsolutePosition & " - " & ii
        If rs2.NoMatch = True Then
            With rs2
                .AddNew
                ![DefDate] = rs("DefDate")
                ![Shop] = rs("Shop")
                ![Project] = rs("Project")
                ![Nuc] = rs("Nuc")
                ![R] = rs("R")
                ![c] = rs("C")
                .Update
            End With
        Else
            With rs2
                .Edit
                ![R] = rs("R")
                ![c] = rs("C")
                .Update
            End With
        End If
    rs.MoveNext
Loop
rs2.Close
rs.Close
'--------------------------------------------------------------------------End SS Metrics-------------------------------------------------------------------------------------
StatusLabel "Completed!"
Set rs2 = Nothing
Set rs = Nothing
Set db = Nothing
End Function

 

[Rx_]

This seems to perform a lot of searching / editing while the same Recordset is open. So it is good to open a Dynaset on a Query / SQL statement
But, it appears that this code sets a cursor to move record by record - determine if a record should be updated based on the * (all) records.
The Set rs2 on the last code segment should try to limit as much as possible to reduce the number of records returned. The Select * is basically opening all records. This results in a N/2 search. (number of records divided by 2) just to find the first record that meets the edit search criteria.
This is not as efficient as putting the IBB Date and Home Shop ID inside the Select statement to filter the records in your Select Statement.
This code appears to pick all records first, then starts a cursor at the top record and goes record by record.
A select statement with your parameters (filters) would pre-compile and use the indexes. With the much smaller result set, the code could run the updates or edits an the smaller number of recordsets much faster.

If the resulting records all have the same fields updated or edited in the same way, the opportunity for an Execute SQLStatement exist. Executing a SQL update is very fast.

Select * where {criteria1], [criteria2], [date range1] .... (dynamic filtering used here to limit result record size)
Then on the smaller set of resulting records - run the code to edit and update

 

[thechazm]

Thanks for the info and the different perspective on the current issue. I have done some more investigating and found that when adding the index Test01 like I did the searches were in fact very fast but the updates to the dataset were increadably slow even to the point where it would take 1.5 seconds to update a single record with the index in place...

I do like the idea of running a SQL statement and may go that route but it would have to be two SQL statements for each section I believe. One to add the new records and one to update the existing ones.

The purpose of this table in a whole is having the metrics pre-built daily with the applicable dates in the table so when data crunchers or higher ups take a look at the data they are not waiting it to compile the data every time. We run an update once a day to provide the updated data and we take in 1 months worth of data every day.

So when this function runs it updates an entire months worth of data every day.

One thing that I don't feel will work is opening the recordset with a filter on it over and over again because establishing and disconnecting the recordset that many times for each section will probably add more time to the update.

Again thank you very much for the info and I'll try a different approach by using the SQL Add and update meathods to see if I can get it to perform faster.

Ow and when I go that route do you know of an efficiant way to display to the user either how soon the query will be done or some kind of a status other than the little text and bar at the bottom right? I remember doing something like this using windowhooks or something but I can't remember for sure.

Thanks again

 

[Rx_]

Forgot to ask if you are running in a muli-user envrionment? Record locking is another issue of your addnew statements with a cursor.
http://www.access-programmers.co.uk/forums/showthread.php?t=206265
Here is a thread to get you started on the Workspace object Transaction (and rollback).
Some Office 2010 vba code can be found here:
http://msdn.microsoft.com/en-us/library/office/ff822782.aspx
A Workspace actually starts a new session.
Given the size and frequency of your task, this would be worth your time to look into.
Also... on the rollback - it is not uncommon to add a counter to retry three times in case a record is being used or is locked.

This is just another level of programming that can prevent problems as more users link into the back-end databae.

 

[thechazm]

Yes it is a multi-user environment. I did some testing on Access 2007 regarding workspaces and it seemed to be like they really did not operate the way they were intended to in 2007. I do not know if they work correctly in 2010 but in 2003 they worked great.

When I used it in the 2007 version I didn't seem to gain any benifits by using it other than the rollback function. In 2003 it would capture the table in memory thus making a local copy of the dataset being worked on making it really fast on updating. Again I have not tested this in 2010 but again thanks for the info.

 

[thechazm]

So I tried using an simple update query to do what we had discussed earlier and it tells me that it can't use an un-updatable query? Is there any reason why a totals query can not be used in an update query to push the updates to the table?

Here is the SQL I am using:

UPDATE [(Compute) CL Metrics] INNER JOIN [Compiled Metrics] ON ([(Compute) CL Metrics].Nuc = [Compiled Metrics].Nuc) 
AND ([(Compute) CL Metrics].[Project ID] = [Compiled Metrics].[Project ID]) 
AND ([(Compute) CL Metrics].[Home Shop ID] = [Compiled Metrics].[Shop ID]) 
AND ([(Compute) CL Metrics].[IBB Date] = [Compiled Metrics].[Daily Date]) 
SET [Compiled Metrics].[CL Personnel] = [Personnel], 
[Compiled Metrics].[CL Charged Time] = [Charge Total], 
[Compiled Metrics].[CL Daily MPD] = [Daily MPD];

Thanks

 

[Rx_]

http://support.microsoft.com/kb/116142

There are three methods of working around this behavior. (see details)
Is this the symptom? ​

 

[thechazm]

Yes exactly. That figures.

Well I went and decided to modify the code using some static properties to keep the beginning date and ending date then applying them both to my metrics query and my table that I am trying to modify.

The speed was considerably better since it had fewer records to sort through and I eliminated my indexes which allowed it to update fast as well.

This dropped my loading time from 4 hours to roughly less than 1 hour. I appreciate the information and comments earlier.

Here is a small section of code that I changed just so anyone can have a reference if they needed to do the same type of thing.

Set rs = db.OpenRecordset("Select * from [(Compute) CL Metrics]", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [Compiled Metrics] WHERE((([Daily Date]) Between #" & GetChargesStart & "# AND #" & GetChargesEnd & "#))", dbOpenDynaset)

 

[Rx_]

http://msdn.microsoft.com/en-us/library/office/ff197799.aspx
See Code Example at this link:
Could I please bother you to choose the option DBForwardOnly

Forward-only-type Recordset— identical to a snapshot except that no cursor is provided. You can only scroll forward through records. This improves performance in situations where you only need to make a single pass through a result set. This type corresponds to an ODBC forward-only cursor.
Back when I taught Access 97 (yes I taught cave men to program and I was still the "old guy") DBForwardOnly was really faster. I wrote a popular Access Programming Cert Test Prep exam - and this was on the test.
As the name implies, it goes from top to bottom, can't back up. It was known as the "firehose" cursor.
I did some testing on Access 2007 a while back and there was no difference.
It might be that my Window Server and processors are running applications from RAM so it doesn't really matter anymore.

Since you have some large recordsets and some good timing defined, I would appreciate you publishing your experience.
If it runs way faster, I will accept the credit (LOL) if it doesn't lets never mention DBForwardOnly again... - Thanks​

 

[thechazm]

Haha no problem I'll give it a shot with today's data and see what I get from it.

Thanks.

 

[thechazm]

Well ok I did the comparison on the function in a whole between the two dbopentypes and to be fair both functions maxed out my cpu so I don't know how real this is going to be but here are my specs on the CPU.

Intel Core 2 Duo
E6550 @ 2.33Ghz

Attached is the excel document I made with the data. They were pretty much neck and neck but the dbopensnapshot was just a bit faster.

See attached excel document for the results.View attachment dbopentype comparison.xls

I was able to modify/add 89371 many records in the time shown in the documents.