E
EagleOne
2003
The routines which follow work fine with 15,000 records.
When the file size gets larger I do get overflow errors.
Background: The VBA routines up to this point have read text files into two Tables.
Table one "STARS" Table two "CHOOSE"
All has been successful until I decided to save Record Counts and Dollar totals by Category. My
desire was to use the MemVars to transfer into an Excel w/s to be created below the following code.
There are about 40 total MemVars 20 record counts and 20 $ (sub) totals by category.
I am sure that my routines are poorly (not smartly) handled and I need ASARP help as to the best way
to either save the MemVars (in groups of i.e 5?) to a file (another Table?) and/or clear the MemVars
and then go on to the next 5 MemVars until 40 is reached.
All this is being done in VBA (SQL).
I have for sure ID'd the area of overflow to the following memvar section.
Sample Code:
strSQL = "SELECT COUNT(*) AS R5CCount FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseCount = IIf(IsNull(rsCurr!R5CCount), 0, rsCurr!R5CCount)
strSQL = "SELECT Sum([AMT]) AS R5CAMT FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseAMT = IIf(IsNull(rsCurr!R5CAMT), 0, rsCurr!R5CAMT)
strSQL = "SELECT COUNT(*) AS R6CCount FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseCount = IIf(IsNull(rsCurr!R6CCount), 0, rsCurr!R6CCount)
strSQL = "SELECT Sum([AMT]) AS R6CAMT FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseAMT = IIf(IsNull(rsCurr!R6CAMT), 0, rsCurr!R6CAMT)
...... This continues until 40 Memvars are created ......
As mentioned, the logic works but it blows up with big files
What steps should I take to do the above smartly? Beggers can not be choosers - but help soon
please.
I do not need help with Automation etc, all that works fine.
TIA
EagleOne
The routines which follow work fine with 15,000 records.
When the file size gets larger I do get overflow errors.
Background: The VBA routines up to this point have read text files into two Tables.
Table one "STARS" Table two "CHOOSE"
All has been successful until I decided to save Record Counts and Dollar totals by Category. My
desire was to use the MemVars to transfer into an Excel w/s to be created below the following code.
There are about 40 total MemVars 20 record counts and 20 $ (sub) totals by category.
I am sure that my routines are poorly (not smartly) handled and I need ASARP help as to the best way
to either save the MemVars (in groups of i.e 5?) to a file (another Table?) and/or clear the MemVars
and then go on to the next 5 MemVars until 40 is reached.
All this is being done in VBA (SQL).
I have for sure ID'd the area of overflow to the following memvar section.
Sample Code:
strSQL = "SELECT COUNT(*) AS R5CCount FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseCount = IIf(IsNull(rsCurr!R5CCount), 0, rsCurr!R5CCount)
strSQL = "SELECT Sum([AMT]) AS R5CAMT FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseAMT = IIf(IsNull(rsCurr!R5CAMT), 0, rsCurr!R5CAMT)
strSQL = "SELECT COUNT(*) AS R6CCount FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseCount = IIf(IsNull(rsCurr!R6CCount), 0, rsCurr!R6CCount)
strSQL = "SELECT Sum([AMT]) AS R6CAMT FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseAMT = IIf(IsNull(rsCurr!R6CAMT), 0, rsCurr!R6CAMT)
...... This continues until 40 Memvars are created ......
As mentioned, the logic works but it blows up with big files
What steps should I take to do the above smartly? Beggers can not be choosers - but help soon
please.
I do not need help with Automation etc, all that works fine.
TIA
EagleOne