VBA code in Access adp stops running half way through, out of memory

[Barntek]

Hello,

I have written some VBA code in a adp in access using a SQL2K database. The code runs mostly SQL statements using the '.execute' command. There are several loops in the code which also use some recordsets in the data.

The code runs fine on my machine, taking about 15 mins to run. However when i pass it to my colleague (who has the same amount of memory but a slower processer) then it stops running half way through claiming to be out of memory; other programs still can be used at this point but Access can not, closing and reopening access does not free the memory. Both machines are running Access 2003.

What can i do to free the memory in Access after each loop of the program, or is there a setting that must be different between machines, or any other suggestions would be helpful

Thank you greatly
Barney

 

[cmmrfrds]

Can you paste in the offending code.

 

[Barntek]

Hello,

The code is below, with the function listed below that.
Thanks for your time

Barney

******************************************************
Public Sub Profile_Click()

On Error GoTo err1

Dim CON As ADODB.Connection
Dim rstGROUPING As New ADODB.Recordset
Dim i As Integer
Dim a As Double
Dim b As Double
Dim TOT As Double
Dim tot2 As Double
Dim STEP As Integer
Dim COUNT As Integer
Dim GROUPSTEP As Integer
Dim strGROUPING As String


Set CON = CurrentProject.Connection
CON.CommandTimeout = 0
CON.CursorLocation = adUseServer

'this imput box is hidden and set to 17, however this check is left in
'17 is considered to be the appropraite length of checking
If Form_control!cycles = "" Or Form_control!cycles < 1 Or IsNull(Form_control!cycles) = True Then
MsgBox "Incorrect number of cycles"
Exit Sub
End If


'update metre
SysCmd acSysCmdInitMeter, "Building", Form_control!cycles
SysCmd acSysCmdUpdateMeter, 0

COUNT = 0
GROUPSTEP = 0

'copy data out of the summary table, to a temp table. this data is the selcted back to the summary table for analysis
'based on the later selection by grouping
If Form_control!by_range = True Or Form_control!by_category = True Or Form_control!by_week Then
CON.Execute "delete tbl_summary_temp", , adExecuteNoRecords
CON.Execute "insert into tbl_summary_temp select * from tbl_summary", , adExecuteNoRecords
CON.Execute "delete tbl_summary", , adExecuteNoRecords
End If

'delete old results table
CON.Execute "delete tbl_despatch_profile_grouped", , adExecuteNoRecords

'build new grouping table based on the selection used
If Form_control!by_range = True Then
CON.Execute "delete tbl_grouping", , adExecuteNoRecords
CON.Execute "insert into tbl_grouping select range from tbl_summary_temp group by range order by range", , adExecuteNoRecords
End If

If Form_control!by_category = True Then
CON.Execute "delete tbl_grouping", , adExecuteNoRecords
CON.Execute "insert into tbl_grouping select category from tbl_summary_temp group by category order by category", , adExecuteNoRecords
End If

If Form_control!by_week = True Then
CON.Execute "delete tbl_grouping", , adExecuteNoRecords
CON.Execute "insert into tbl_grouping select data_week from tbl_summary_temp group by data_week order by data_week", , adExecuteNoRecords
End If

If Form_control!by_all = True Then GoTo skipgrouping

'to be able to analysis the correct data, based on the grouping value then the data is moved back to the summary table
'based on a selection of thed grouping table, this is done by selecting data 'grouping' by 'grouping'
rstGROUPING.Open "tbl_grouping", CON, adOpenKeyset, adLockOptimistic
rstGROUPING.MoveFirst

'do the analysis for all datasets for each grouping value
Do Until rstGROUPING.EOF

GROUPSTEP = GROUPSTEP + 1

skipgrouping:


'select data set based on grouping value
If Form_control!by_range = True Then
CON.Execute "insert into tbl_summary select * from tbl_summary_temp where range = '" & rstGROUPING!grouping & "'", , adExecuteNoRecords
End If

If Form_control!by_category = True Then
CON.Execute "insert into tbl_summary select * from tbl_summary_temp where category = '" & rstGROUPING!grouping & "'", , adExecuteNoRecords
End If

If Form_control!by_week = True Then
CON.Execute "insert into tbl_summary select * from tbl_summary_temp where data_week = '" & rstGROUPING!grouping & "'", , adExecuteNoRecords
End If

'drop the old analysis table
CON.Execute "delete tbl_profile_summary", , adExecuteNoRecords

'build order book analysis table and tidy
CON.Execute "select measure, data_week, year_week, sum(value) as value into temp_profile_summary from tbl_summary group by measure, data_week, year_week, value", , adExecuteNoRecords
CON.Execute "insert into tbl_profile_summary select measure, year_week, sum(value) as value from temp_profile_summary group by measure, year_week", , adExecuteNoRecords
CON.Execute "update tbl_profile_summary set year_week = 'Unallocated' where year_week is null", , adExecuteNoRecords
CON.Execute "update tbl_profile_summary set year_week = 'Holding' where year_week like '5.2999e+009'", , adExecuteNoRecords
CON.Execute "drop table temp_profile_summary", , adExecuteNoRecords
CON.Execute "delete tbl_profile_analysis", , adExecuteNoRecords

'trap where there is not data in profile
If DSum("value", "tbl_profile_summary") = 0 Then
COUNT = COUNT + 1
GoTo skiptonew2
End If

'build %analysis of order analysis table
CON.Execute "insert into tbl_profile_analysis select 'orders' as measure, year_week, value/(select sum(value)as v1 from tbl_profile_summary where measure = 'orders') as value from tbl_profile_summary where measure = 'orders' and (select sum(value)as v1 from tbl_profile_summary where measure = 'orders')<>0", , adExecuteNoRecords
CON.Execute "insert into tbl_profile_analysis select 'date chg in bf' as measure, year_week, value/(select sum(value)as v2 from tbl_profile_summary where measure = 'date chg in bf') as value from tbl_profile_summary where measure = 'date chg in bf' and (select sum(value)as v2 from tbl_profile_summary where measure = 'date chg in bf') <>0", , adExecuteNoRecords
CON.Execute "insert into tbl_profile_analysis select 'date chg in def' as measure, year_week, value/(select sum(value)as v3 from tbl_profile_summary where measure = 'date chg in def') as value from tbl_profile_summary where measure = 'date chg in def' and (select sum(value)as v3 from tbl_profile_summary where measure = 'date chg in def') <>0 ", , adExecuteNoRecords
CON.Execute "insert into tbl_profile_analysis select 'uncon in' as measure, year_week, value/(select sum(value)as v4 from tbl_profile_summary where measure = 'uncon in' and year_week <> 'unallocated') as value from tbl_profile_summary where measure = 'uncon in' and (select sum(value)as v4 from tbl_profile_summary where measure = 'uncon in' and year_week <> 'unallocated') <>0 ", , adExecuteNoRecords
CON.Execute "insert into tbl_profile_analysis SELECT tbl_profile_summary.measure, tbl_profile_summary.year_week, tbl_profile_summary.[value] / tbl_profile_summary_1.[value] AS value FROM tbl_profile_summary INNER JOIN tbl_profile_summary tbl_profile_summary_1 ON tbl_profile_summary.year_week = tbl_profile_summary_1.year_week WHERE (tbl_profile_summary_1.measure = 'outstanding orders') AND (tbl_profile_summary_1.value <>0) AND (tbl_profile_summary.measure = 'date chg out def' OR tbl_profile_summary.measure = 'date chg out bf' OR tbl_profile_summary.measure = 'uncon out')", , adExecuteNoRecords
CON.Execute "insert into tbl_profile_analysis SELECT measure, year_week, (SELECT SUM(tbl_profile_summary.value) AS value FROM tbl_profile_summary WHERE tbl_profile_summary.measure = 'holding conv' AND year_week = 'holding') / (SELECT SUM(tbl_profile_summary.value) AS value FROM tbl_profile_summary WHERE tbl_profile_summary.measure = 'outstanding orders') * [value] / (SELECT SUM(tbl_profile_summary.value) AS value FROM tbl_profile_summary WHERE tbl_profile_summary.measure = 'holding conv' AND year_week <> 'holding') AS value FROM tbl_profile_summary WHERE (measure = 'holding conv') and (SELECT SUM(tbl_profile_summary.value) AS value FROM tbl_profile_summary WHERE tbl_profile_summary.measure = 'outstanding orders') <> 0 and (SELECT SUM(tbl_profile_summary.value) AS value FROM tbl_profile_summary WHERE tbl_profile_summary.measure = 'holding conv' AND year_week <> 'holding')<>0", , adExecuteNoRecords


'reset profile to 0
CON.Execute "update tbl_profile set [value] = 0", , adExecuteNoRecords

'populate current orders
CON.Execute "update tbl_profile set tbl_profile.[value] = tbl_profile_summary.value from tbl_profile inner join tbl_profile_summary on tbl_profile.year_week_var = tbl_profile_summary.year_week and tbl_profile.measure = tbl_profile_summary.measure where tbl_profile.measure = 'orders'", , adExecuteNoRecords

'trap where there is not data in profile
If DSum("value", "tbl_profile", "measure = 'orders' and year_week_int is not null") = 0 Then
COUNT = COUNT + 1
GoTo skiptonew
End If


'clear DESPATCH profile
CON.Execute "update tbl_despatch_profile set value = 0, [percent] = 0", , adExecuteNoRecords


'check that the process is run for the correct number of cycles (default is 17)
STEP = 0
Do Until STEP = Form_control!cycles + 1

'update metre
If Form_control!by_all = False Then
SysCmd acSysCmdInitMeter, "Building (" & GROUPSTEP & " of " & DCount("grouping", "tbl_grouping") & ") " & rstGROUPING!grouping, Form_control!cycles
SysCmd acSysCmdUpdateMeter, STEP
Else
SysCmd acSysCmdInitMeter, "Building all", Form_control!cycles
SysCmd acSysCmdUpdateMeter, STEP
End If

'below are the analyis steps whcih calculate the values coming in and out of the order book,
'these are done in part groups

If Form_control!by_all = False Then
strGROUPING = rstGROUPING!grouping
rstGROUPING.close
Set rstGROUPING = Nothing
End If

CON.close
Set CON = Nothing

run_profile_step_build_function

Set CON = CurrentProject.Connection
CON.CommandTimeout = 0

If Form_control!by_all = False Then
rstGROUPING.Open "tbl_grouping", CON, adOpenKeyset, adLockOptimistic
rstGROUPING.MoveFirst
rstGROUPING.Find "grouping = '" & strGROUPING & "'", 0, adSearchForward, 0
End If


CON.Execute "update tbl_despatch_profile set tbl_despatch_profile.value = (select sum(value) as value from tbl_profile where tbl_profile.year_week_var = '0') where tbl_despatch_profile.year_week = '" & STEP & "'", , adExecuteNoRecords

'calculate the new order profile based on the values of items coming 'in' and 'out'
i = 1
Do Until i = 40

TOT = DSum("value", "tbl_profile", "year_week_int = " & i)

'the new orders profile is 'shifted' up by one week
CON.Execute "update tbl_profile set value = " & TOT & " where year_week_int = " & i - 1 & " and measure = 'orders'", , adExecuteNoRecords

i = i + 1
Loop

STEP = STEP + 1
Loop

'update percentage of results
If DSum("value", "tbl_despatch_profile") <> 0 Then
CON.Execute "update tbl_despatch_profile set tbl_despatch_profile.[percent] = [value] / (select sum([value]) as [value] from tbl_despatch_profile) from tbl_despatch_profile", , adExecuteNoRecords
End If

'set weeks_used if not 0
If DSum("value", "tbl_despatch_profile") <> 0 Then
CON.Execute "update tbl_despatch_profile set tbl_despatch_profile.weeks_used = (select max(year_week) from tbl_despatch_profile where value <> 0)", , adExecuteNoRecords
End If

'set weeks_used if 0
If DSum("value", "tbl_despatch_profile") = 0 Then
CON.Execute "update tbl_despatch_profile set tbl_despatch_profile.weeks_used = 0", , adExecuteNoRecords
End If


'copy to grouped output for 'all'
If Form_control!by_all = True Then
CON.Execute "insert into tbl_despatch_profile_grouped select 'all' as grouping, * from tbl_despatch_profile where tbl_despatch_profile.year_week <= tbl_despatch_profile.weeks_used", , adExecuteNoRecords
GoTo exit_loop
End If

'copy to group output for other
CON.Execute "insert into tbl_despatch_profile_grouped select '" & rstGROUPING!grouping & "' as grouping, * from tbl_despatch_profile where year_week <= weeks_used", , adExecuteNoRecords

skiptonew:

skiptonew2:

rstGROUPING.MoveNext
CON.Execute "delete tbl_summary", , adExecuteNoRecords

SysCmd acSysCmdUpdateMeter, 0
DoEvents
Loop

'delete summary table and copy back the whole data set
CON.Execute "delete tbl_summary", , adExecuteNoRecords
CON.Execute "insert into tbl_summary select * from tbl_summary_temp", , adExecuteNoRecords

exit_loop:

rstGROUPING.close
Set rstGROUPING = Nothing

'set weeks_used to + 1 to accommodate week 0
CON.Execute "update tbl_despatch_profile_grouped set tbl_despatch_profile_grouped.weeks_used = tbl_despatch_profile_grouped.weeks_used + 1", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped set tbl_despatch_profile_grouped.year_week = tbl_despatch_profile_grouped.year_week + 1", , adExecuteNoRecords

'make numbers a rounded %
CON.Execute "update tbl_despatch_profile_grouped set tbl_despatch_profile_grouped.[percent] = round((tbl_despatch_profile_grouped.[percent]*100),2)", , adExecuteNoRecords

'transpose output
'build table
CON.Execute "delete tbl_despatch_profile_grouped_trans", , adExecuteNoRecords
CON.Execute "insert into tbl_despatch_profile_grouped_trans select grouping, cast(0 as float) as MFI_WK1, cast(0 as float) as MFI_WK2, cast(0 as float) as MFI_WK3, cast(0 as float) as MFI_WK4, cast(0 as float) as MFI_WK5, cast(0 as float) as MFI_WK6, cast(0 as float) as MFI_WK7, cast(0 as float) as MFI_WK8, cast(0 as float) as MFI_WK9, cast(0 as float) as MFI_WK10, cast(0 as float) as MFI_WK11, cast(0 as float) as MFI_WK12, cast(0 as float) as MFI_WK13, cast(0 as float) as MFI_WK14, cast(0 as float) as MFI_WK15, cast(0 as float) as MFI_WK16, cast(0 as float) as MFI_WK17, cast(0 as float) as MFI_WK18, 0 as MFI_NUM_WEEKS from tbl_despatch_profile_grouped group by grouping ", , adExecuteNoRecords

CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK1 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 1", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK2 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 2", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK3 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 3", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK4 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 4", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK5 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 5", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK6 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 6", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK7 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 7", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK8 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 8", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK9 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 9", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK10 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 10", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK11 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 11", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK12 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 12", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK13 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 13", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK14 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 14", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK15 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 15", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK16 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 16", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK17 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 17", , adExecuteNoRecords
CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_WK18 = tbl_despatch_profile_grouped.[percent] from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 18", , adExecuteNoRecords

CON.Execute "update tbl_despatch_profile_grouped_trans set MFI_NUM_WEEKS = tbl_despatch_profile_grouped.weeks_used from tbl_despatch_profile_grouped inner join tbl_despatch_profile_grouped_trans on tbl_despatch_profile_grouped.grouping = tbl_despatch_profile_grouped_trans.grouping where tbl_despatch_profile_grouped.year_week = 1", , adExecuteNoRecords


SysCmd acSysCmdRemoveMeter

If Form_control!check = 0 Then
MsgBox ("Done with " & COUNT & " profile(s) skipped due to no data")
End If

err_exit:
On Error Resume Next
If Not rstGROUPING Is Nothing Then
If rstGROUPING.State = adStateOpen Then
rstGROUPING.close
End If
Set rstGROUPING = Nothing
End If
If Not CON Is Nothing Then
If CON.State = adStateOpen Then
CON.close
End If
Set CON = Nothing
End If
Exit Sub

err1:
MsgBox err.Description
GoTo err_exit


End Sub

*****************************************************
*****************************************************
Function run_profile_step_build_function()

Dim CON As ADODB.Connection

Set CON = CurrentProject.Connection
CON.CommandTimeout = 0
CON.CursorLocation = adUseServer



'what is being taken 'out'
CON.Execute "update tbl_profile set value = 0 where tbl_profile.measure = 'date chg out bf' or tbl_profile.measure = 'date chg out def' or tbl_profile.measure = 'uncon out'", , adExecuteNoRecords

CON.Execute "select * into tbl_temp3 from tbl_profile where measure = 'orders'", , adExecuteNoRecords

CON.Execute "update tbl_profile set value = tbl_profile_analysis.value * tbl_temp3.value FROM tbl_profile INNER JOIN " _
& " tbl_profile_analysis ON tbl_profile.year_week_var = tbl_profile_analysis.year_week AND " _
& " tbl_profile.measure = tbl_profile_analysis.measure INNER JOIN " _
& " tbl_temp3 ON tbl_profile_analysis.year_week = tbl_temp3.year_week_var where " _
& " (tbl_profile.year_week_int > 0 or tbl_profile.year_week_int is null) and (tbl_profile.measure = 'date chg out bf' or tbl_profile.measure = 'date chg out def' or tbl_profile.measure = 'uncon out')", , adExecuteNoRecords

CON.Execute "drop table tbl_temp3", , adExecuteNoRecords


''def in'
CON.Execute "update tbl_profile set value = 0 where tbl_profile.measure = 'date chg in def' ", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = tbl_profile_analysis.value * -(select sum(value) as value from tbl_profile " _
& " where tbl_profile.measure = 'date chg out def') from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile.measure = tbl_profile_analysis.measure " _
& " where tbl_profile.measure = 'date chg in def' and (tbl_profile.year_week_int > 0 and tbl_profile.year_week_int is not null)", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = tbl_profile_analysis.value * (select sum(value) as value from tbl_profile " _
& " where tbl_profile.measure = 'date chg out def') from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile.measure = tbl_profile_analysis.measure " _
& " where tbl_profile.measure = 'date chg in def' and (tbl_profile.year_week_int is null)", , adExecuteNoRecords


''bf in'
CON.Execute "update tbl_profile set value = 0 where tbl_profile.measure = 'date chg in bf' ", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = tbl_profile_analysis.value * -(select sum(value) as value from tbl_profile " _
& " where tbl_profile.measure = 'date chg out bf') from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile.measure = tbl_profile_analysis.measure" _
& " where tbl_profile.measure = 'date chg in bf' and (tbl_profile.year_week_int > 0 and tbl_profile.year_week_int is not null)", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = tbl_profile_analysis.value * (select sum(value) as value from tbl_profile " _
& " where tbl_profile.measure = 'date chg out bf') from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile.measure = tbl_profile_analysis.measure" _
& " where tbl_profile.measure = 'date chg in bf' and (tbl_profile.year_week_int is null)", , adExecuteNoRecords


''uncon in'
CON.Execute "update tbl_profile set value = 0 where tbl_profile.measure = 'uncon in' ", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = tbl_profile_analysis.value * (select sum(value) as value from tbl_profile " _
& " where tbl_profile.measure = 'uncon out' and year_week_var = 'Unallocated') from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile.measure = tbl_profile_analysis.measure" _
& " where tbl_profile.measure = 'uncon in' and (tbl_profile.year_week_int > 0 and tbl_profile.year_week_var <> 'Unallocated')", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = tbl_profile_analysis.value * -(select sum(value) as value from tbl_profile " _
& " where tbl_profile.measure = 'uncon out' and year_week_var = 'unallocated') from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile.measure = tbl_profile_analysis.measure" _
& " where tbl_profile.measure = 'uncon in' and (tbl_profile.year_week_var = 'Unallocated')", , adExecuteNoRecords



''holding'
CON.Execute "update tbl_profile set value = 0 where tbl_profile.measure = 'holding conv' ", , adExecuteNoRecords

CON.Execute " update tbl_profile set value = " _
& " ((select sum(value) as value from tbl_profile_summary where tbl_profile_summary.measure = 'holding conv' and tbl_profile_summary.year_week = 'holding')/" _
& " (select sum(value) as value from tbl_profile_summary where tbl_profile_summary.measure = 'outstanding orders' and tbl_profile_summary.year_week = 'holding')) * " _
& " (select sum(value) as value from tbl_profile where tbl_profile.measure = 'orders' and tbl_profile.year_week_var = 'holding')* " _
& " tbl_profile_analysis.value from tbl_profile inner join tbl_profile_analysis on tbl_profile.year_week_var = tbl_profile_analysis.year_week and tbl_profile_analysis.measure = tbl_profile.measure" _
& " where tbl_profile.measure = 'holding conv' and (tbl_profile.year_week_int > 0 or tbl_profile.year_week_int is null) and (select sum(value) as value from tbl_profile_summary where tbl_profile_summary.measure = 'outstanding orders' and tbl_profile_summary.year_week = 'holding') <> 0", , adExecuteNoRecords

'calculate despatches and add to the results table for this grouping

CON.Execute "dbcc freeproccache", , adExecuteNoRecords

CON.close
Set CON = Nothing

End Function

 

[cmmrfrds]

Whew, that is a lot of code to look at. Instead of creating and destorying a connection object(s), why not use currentproject.connection or set up a connection in a public module and create 1 time at the beginning of the process and destroy at the end. I would try this first.

 

[Barntek]

Hello,

Initially i did create a single connection, run all the code and then disconnect once finished. However, the problem occured and since then i have tried a variety of things to try to make it run. One of these things was to keep disconnecting and reconnecting, which was intended to make sure that there was no residual memory issues with the connection, however this did not seem to work.

Thankyou greatly for your time
Barney

 

[cmmrfrds]

Have you tried commenting out the sysCmd.

Is this a new app or upsized?

What service pack of Office, MDAC, etc....
What version of Access?

 

[Barntek]

Hello,

I have not tried not using the syscmd but will do - is this lightly to make a difference? The application is new and has always had this same issue.

I am running Office XP (2002) with SP2, and Access 2003.

I was talking to someone last night about this issus and he suggested that the processer speed had more of an impact than first thought. Could it be possible that with a quicker processer less is pushed into memory/cache and so the code can run through without issue. If this were the case then how could i avoid this scenario?

Thanks
B

 

[cmmrfrds]

I would be surprised if out of memory error was associated with processor speed. Normally, it is because memory is used and not released or a lot of data is being brought into memory.

The process seems very inefficient if you truely are using temporary tables. It looks like the process is creating and deleting data from sql server tables. Unless you have logging turned off it is taking many times the time needed for this process.

If these are temporary tables they should be created as #temp tables in the sql server temp table area. This would be many times more efficient.