Hello All - to give a little primer on what i am doing. I am building an app that takes a weekly extract and populates a table for analysis. I am tracking my data by week. I have 3 table that are in question here:
1. My main table holding historical data. Has a field for contract id(indexed to accept no dups) and one field for each fiscal week of the year.
2. Temp table that is populated with weekly import. Has contract id, fiscal week and sales.
3. A table that holds one record for the current fiscal week.
My main table holds all the contracts i am tracking for my division (roughly 12m lines). I have a field for each fiscal week (1,2,3,4,ect.).
The temp table holds all contracts for one week being uploaded. Usually will be about 6m contracts that had sales the previous week.
The current fiscal week is extracted from the temp table and placed in the third table. This record is used to tell the routine which field to upload data to.
This only a piece of my overal app but I cannot continue until get this down since my data analysis/retrival is dependent on my main table.
I have written a piece of code that works the way I want it to but it takes about 25mins to run. A buddy of mine suggested SQL Batch Update, but I have barely read about this and never tried.
If someone can take a look at this code and maybe have a better solution I would be much appreciated.
Thanks!
Private Const tblPMSales As String = "tblPriceMethods_Sales"
Private Const tblPMImport As String = "tblPriceMethodsTemp"
Private Const tblWeek As String = "tblWeek"
Private Const fldWk As String = "Week"
Private Const fldContract As String = "Contract ID"
Private Const fldPMImportSalse As String = "Total Sales"
Sub PMUpdate()
Dim rstPM As ADODB.Recordset
Dim rstPMImp As ADODB.Recordset
Dim rstWK As ADODB.Recordset
Dim i As String
Set rstPM = New ADODB.Recordset
Set rstPMImp = New ADODB.Recordset
Set rstWK = New ADODB.Recordset
rstPM.CursorLocation = adUseServer
rstPM.Open tblPMSales, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect 'Main Table
rstWK.Open tblWeek, CurrentProject.Connection, adOpenForwardOnly ' Table holding current fiscal week
rstPMImp.Open tblPMImport, CurrentProject.Connection, adOpenForwardOnly ' Table holding uploaded data
Do While Not rstPMImp.EOF
rstPM.MoveFirst
Do While Not rstPM.EOF
i = rstWK.Fields(fldWk)
If rstPM.Fields(fldContract) = rstPMImp.Fields(fldContract) Then
rstPM.Fields(i) = rstPMImp.Fields(fldPMImportSalse)
End If
rstPM.MoveNext
Loop
rstPMImp.MoveNext
Loop
ExitHere:
On Error Resume Next
rstWK.Close
rstPMImp.Close
rstPM.Update
rstPM.Close
Set rstWK = Nothing
Set rstPM = Nothing
Set rstPMImp = Nothing
Exit Sub
End Sub
1. My main table holding historical data. Has a field for contract id(indexed to accept no dups) and one field for each fiscal week of the year.
2. Temp table that is populated with weekly import. Has contract id, fiscal week and sales.
3. A table that holds one record for the current fiscal week.
My main table holds all the contracts i am tracking for my division (roughly 12m lines). I have a field for each fiscal week (1,2,3,4,ect.).
The temp table holds all contracts for one week being uploaded. Usually will be about 6m contracts that had sales the previous week.
The current fiscal week is extracted from the temp table and placed in the third table. This record is used to tell the routine which field to upload data to.
This only a piece of my overal app but I cannot continue until get this down since my data analysis/retrival is dependent on my main table.
I have written a piece of code that works the way I want it to but it takes about 25mins to run. A buddy of mine suggested SQL Batch Update, but I have barely read about this and never tried.
If someone can take a look at this code and maybe have a better solution I would be much appreciated.
Thanks!
Private Const tblPMSales As String = "tblPriceMethods_Sales"
Private Const tblPMImport As String = "tblPriceMethodsTemp"
Private Const tblWeek As String = "tblWeek"
Private Const fldWk As String = "Week"
Private Const fldContract As String = "Contract ID"
Private Const fldPMImportSalse As String = "Total Sales"
Sub PMUpdate()
Dim rstPM As ADODB.Recordset
Dim rstPMImp As ADODB.Recordset
Dim rstWK As ADODB.Recordset
Dim i As String
Set rstPM = New ADODB.Recordset
Set rstPMImp = New ADODB.Recordset
Set rstWK = New ADODB.Recordset
rstPM.CursorLocation = adUseServer
rstPM.Open tblPMSales, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect 'Main Table
rstWK.Open tblWeek, CurrentProject.Connection, adOpenForwardOnly ' Table holding current fiscal week
rstPMImp.Open tblPMImport, CurrentProject.Connection, adOpenForwardOnly ' Table holding uploaded data
Do While Not rstPMImp.EOF
rstPM.MoveFirst
Do While Not rstPM.EOF
i = rstWK.Fields(fldWk)
If rstPM.Fields(fldContract) = rstPMImp.Fields(fldContract) Then
rstPM.Fields(i) = rstPMImp.Fields(fldPMImportSalse)
End If
rstPM.MoveNext
Loop
rstPMImp.MoveNext
Loop
ExitHere:
On Error Resume Next
rstWK.Close
rstPMImp.Close
rstPM.Update
rstPM.Close
Set rstWK = Nothing
Set rstPM = Nothing
Set rstPMImp = Nothing
Exit Sub
End Sub