limitations to VBA 2

[Zeroanarchy]

Hey, I seem to be having some problems with Access 2003, "
Sorry for the inconvenience". I have been hunting the web for answers and tried everyone. My question is what’s the limitation to an array in Access. Is there one? I have limited the problem down to a section of code that runs an array that needs to handle over 100,000 records. Has anyone come across this issue before or does anyone know if there is a limit to an array size.

I did notice that if I killed a few processes the Array would reach further than previous attempts. What’s interesting is that on some occasions the lot of it works but this is rare, 1 in every 10.

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Crowley16]

the array object uses available system memory, so would depend on how much ram/page file you have.

generally an array isn't that effecient so if you've got 100,000 then it would be a pretty bad idea to use an array. in fact, I wouldn't use an array if there's going to be more than about 1000...

would it be possible to use a temporary table to house this information, and pull back the results in a recordset of some sort?

--------------------
Procrastinate Now!

 

[Zeroanarchy]

Hey Crowley, it would be some what possible, I will certainly work on that tomorrow.

Here is where it gets interesting. I have run the same process on a standalone PC with 2.4cpi and 2.7gig of memory and the program worked well. My other system is a 3gig processor but only has 1gig of memory. The 3gig system is the system that keeps falling over. After running many test on the 2.4gig system the only way I could get it to crash was if I ran it and second time without closing down Access. If I closed access down, restarted it, it would work every time.

I assume this means that access hold a certain amount of data to memory and every time you close Access it clears the memory.

Does anyone know what memory it is using and if there is a way of extending it besides increasing the machines memory?

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[ZmrAbdulla]

Sub SomeSub()

' declares a dynamic array variable
Dim SomeString() As String 
.....
......
......
' deletes the varible contents, free some memory
    [b]Erase SomeString [/b]
End Sub

may be..?

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[Crowley16]

depends on where you declare the array.

VBA is a managed language, so it will have automatic memory clean up, however you can set global variables which lives throughout the entire lifetime of the application or form depending on how global you make it.

it is a good idea to be more dilligent about clean up, however do you really want your access application to be using that much ram? Remember that your other applications and the o/s itself will also be using memory and once you've used up your ram, you start using page file memory which is about 1000 times slower!

apart from cleaning up, you can be more effecient by always limiting your datatypes to the smallest that will do the job, i.e. never use variants, objects unless there's no way around it...

--------------------
Procrastinate Now!

 

[JoeAtWork]

Do you really need 100,000 records in memory at the same time? I find it hard to believe you can't process them in smaller chunks. You should look for ways to make your code more efficient, rather than buying more memory. You would practically have to devote an entire machine to run this one program - not a very good use of resources.

 

[Zeroanarchy]

Yes a few of you have picked up on the 100,000 + recordset but unfortunately I am working on a program that has been built by a few different people over a period of two years. I am trying not to rebuild it as time is not on my side. So I am tring to find a way of working round it until such time as I can rebuild it.

If I was to rebuild it, I might have to look at moving across to VB.net, as it may handle the load a bit better. Next year I will probably have to handle closer to 200,000 records.

Thank you all for you help.




ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Crowley16]

then next year you'll need a computer with 4+ gig's of ram to run this application...

--------------------
Procrastinate Now!

 

[JoeAtWork]

It doesn't matter which programming language you use, the problem is loading way too many records into memory.
You need a different technique to process the records in smaller batches.

I am also wondering if an SQL statement (perhaps an UPDATE) could accomplish your goals? If you can tell us what the process is supposed to do (or post your code) we might be able to offer alternative suggestions.

 

[Crowley16]

although if you think about it, an int in access is 4bytes, so an array of 100k should be 400kb with overhead maybe double that...

either the access array is extremely ineffecient or he's got huge stuff in the array, or both...

--------------------
Procrastinate Now!

 

[Zeroanarchy]

Each line in the array carries a string which can be up to 130 characters long.

The array is processing over 120,000 records.

The purpose of the system is to generate a file (JavaScript file) which contains over 20,000 lines.
The JavaScript is used for drop down selections on a website. If you select X from drop down 1 then Y's will show up as options in drop down 2 and so on for 5 drop downs. It's the last dropdown that the system can not handle, it stops randomly.

So for this reason an Update query is not really an option.

I have looked into using AJAX but the time and cost to change over are not available at the moment.

I have also considered VB.net but comments by JOEATWORK "It doesn't matter which programming language" are making me rethink this options. Wouldn’t VB.net or VB6 can handle arrays a bit better than Access?

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Zion7]

ZA, I don't know anything about Java but, I understand your objective. Are you Comboboxes holding the arrays,as
the rowsource? or, Are are you prior, converting to an
SQL criteria statement?
or something other Rowsource option?

To all followinng the thread, will a "Collection", be a more
efficient container?
How about GetRows(), GetString()?

Maybe "SELECT txtCountry FROM tblCountry WHERE
Country Like '" & cboCountry & "*'"

cboContinent.Requery

???

 

[CautionMP]

Zeroanarchy,
From what I've read I can't picture what your doing but here are a couple of thoughts:
[ol][li]Instead of using an array have a look at the [tt]Collection[/tt] object. It's newer and more efficient.[/li]
[li]You might try looking at a ADODB Recordset to hold the data. It's easier to work with than a array but I don't know what the affect on memeory is (note below)[/li][/ol]

NOTE: I'm not a fan of temporary recordsets in Access, they cause bloat, but I have created ADODB Recordsets as temporary containers. They can be created completely in code and DO NOT have to be based on a table in the database or external data file.

Just my thoughts,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[Crowley16]

in this situation, definitely use a recordset, you can use vbscript to directly call and initiate the recordset from the web, without having to do any coding in the access database...

I'm not used comboboxes on the web, so not sure if this is possible, but in access, you can directly assign a recordset to the recordset property of a combobox. If this is not possible, you can still use a loop to generate the file, and this will only use enough memory to hold a single line, and some pointers/headder information...

however just to clarify, on the website, you've got a combobox which has over 100k records, just how are users going to select the right one?

--------------------
Procrastinate Now!

 

[Zeroanarchy]

Thanks all for your responses so far. In relation to the JavaScript. I am unable to build queries at site end this is due to the company not wanting the page to refresh on selection of the combo boxes (Apparently better user experience to make the option generate client side as the browser does not require to refresh). This is the reason for creating a 2meg java file which contains all the possible drop down options (So people only have to download a 2meg file, as that is a better user experience).

After sone work I have discovered a short term workaround. After running the code on several computers I discovered that it could be run multiple times on system with lower specks rather than on systems with higher specks.

An example:

I currently use Interl4 with dual processors and 1gig of Ram. On this system it runs to the end 1 in 20 times.

If I run it on a Celeron 1.9ghz with 512meg of RAM it will work every time.

The reason for this was that on the slower system, the computer would use Page file to store information. This was because the processor was running at 100%.

On the faster system the processor only ever reached 100% for about 1second, and in that one second it would die every time. Most of the time the processor would run at 40-50%.

I have posted the last part of the code below to five you an ideas as to how it works, If you have any ideas on a better process please feel free to offer them. In the mean time I will investigate Collection object.

Here is an example of what is spat out into the text file.

function update_item_no(){
 document.mainform.item_no.options.length=0
 document.mainform.item_no.options[0]=new Option("--Select Item Number--", "", false, false)
 document.mainform.item_no.selectedIndex = 0
if (document.mainform.year.options[document.mainform.year.selectedIndex].value == "31/12/9999"
 && document.mainform.state.options[document.mainform.state.selectedIndex].value == "NSW"
 && document.mainform.modality.options[document.mainform.modality.selectedIndex].value == "Ambulance services"
 && document.mainform.cover.options[document.mainform.cover.selectedIndex].value == "Active Sports Saver"
 && document.mainform.provider_type.options[document.mainform.provider_type.selectedIndex].value == "Other Providers"
|| document.mainform.year.options[document.mainform.year.selectedIndex].value == "31/12/9999"
 && document.mainform.state.options[document.mainform.state.selectedIndex].value == "NSW"
 && document.mainform.modality.options[document.mainform.modality.selectedIndex].value == "Ambulance services"
 && document.mainform.cover.options[document.mainform.cover.selectedIndex].value == "Ambo Cover"
 && document.mainform.provider_type.options[document.mainform.provider_type.selectedIndex].value == "Other Providers"
|| document.mainform.year.options[document.mainform.year.selectedIndex].value == "31/12/9999"
 
) {
document.mainform.item_no.options[1]=new Option("1102 -> Emergency Ambulance ...","1102 -> Emergency Ambulance ...", true, false)
}
if (document.mainform.year.options[document.mainform.year.selectedIndex].value == "31/12/9999"
 && document.mainform.state.options[document.mainform.state.selectedIndex].value == "NSW"
 && document.mainform.modality.options[document.mainform.modality.selectedIndex].value == "Ambulance services"
 && document.mainform.cover.options[document.mainform.cover.selectedIndex].value == "Corporate Overseas Visitors Cover"
 && document.mainform.provider_type.options[document.mainform.provider_type.selectedIndex].value == "Other Providers"
|| document.mainform.year.options[document.mainform.year.selectedIndex].value == "31/12/9999"
 && document.mainform.state.options[document.mainform.state.selectedIndex].value == "NT"
 && document.mainform.modality.options[document.mainform.modality.selectedIndex].value == "Ambulance services"
 && document.mainform.cover.options[document.mainform.cover.selectedIndex].value == "Corporate Overseas Visitors Cover"
 && document.mainform.provider_type.options[document.mainform.provider_type.selectedIndex].value == "Other Providers"
) {
document.mainform.item_no.options[1]=new Option("1101 -> All Ambulance Transp...","1101 -> All Ambulance Transp...", true, false)
document.mainform.item_no.options[2]=new Option("1102 -> Emergency Ambulance ...","1102 -> Emergency Ambulance ...", true, false)
document.mainform.item_no.options[3]=new Option("1102 -> Emergency Ambulance ...","1102 -> Emergency Ambulance ...", true, false)
}

Thanks again all for your input on this one.

Last combo box population VBA Code.

Private Sub WriteOptimizedItemNr()
   
    Dim arrCounter, svIndex, ItemNrCounter, intX, intI As Long
    Dim SearchChar, strItemNr, TempItemNr, strItemNrDesc, strYear, strStates, strModality, strCover, strProviderType, svState, svModality, svCover, svItemNr, svProviderType, svDate As String
    Dim SearchPos, StartPos, xrt As Integer
    Dim dbs As DAO.Database
    Dim rs8, rs9, rs0 As Recordset
    
    Set dbs = CurrentDb
    
    ItemNrCounter = 0
    svIndex = 0
    arrCounter = 1
    SearchChar = "|"
    intI = 0
    svIndex = 0
    StartPos = 1

    Set rs8 = dbs.OpenRecordset("SELECT TODATE, SSTATENAME, SERVICECATEGORY, LPRODUCTNAME, LPROVIDERTYPE, PROVIDERCATEGORY, BUSINESSFLAG, ITEMNR, ITEMNRDESC, LBENEFITNAME FROM TEMP_PDLD GROUP BY TODATE, SSTATENAME, SERVICECATEGORY, LPRODUCTNAME, LPROVIDERTYPE, PROVIDERCATEGORY, BUSINESSFLAG, ITEMNR, ITEMNRDESC, LBENEFITNAME HAVING (((TODATE) = #12/31/9999#))ORDER BY TODATE DESC , SSTATENAME, SERVICECATEGORY, LPRODUCTNAME, LPROVIDERTYPE, PROVIDERCATEGORY, BUSINESSFLAG, ITEMNR, ITEMNRDESC, LBENEFITNAME", dbOpenForwardOnly)
    Do While Not rs8.EOF
        
        ItemNrCounter = ItemNrCounter + 1
        If ItemNrCounter = 1 Then
            ReDim arrItemNr(1)
        Else
            ReDim Preserve arrItemNr(UBound(arrItemNr) + 1)
        End If
        If svDate <> rs8!TODATE Or svState <> rs8!SSTATENAME Or svModality <> rs8!SERVICECATEGORY Or svCover <> rs8!LPRODUCTNAME Or svProviderType <> rs8!PROVIDERCATEGORY Then
            If svDate <> "" And svState <> "" And svModality <> "" And svCover <> "" And svProviderType <> "" Then
                arrItemNr(arrCounter) = arrItemNr(arrCounter) & "|"
                arrCounter = arrCounter + 1
            End If
            arrItemNr(arrCounter) = rs8!ITEMNRDESC & "|"
        Else
            arrItemNr(arrCounter) = arrItemNr(arrCounter) & rs8!ITEMNRDESC & "|"
        End If
        svDate = rs8!TODATE
        svState = rs8!SSTATENAME
        svModality = rs8!SERVICECATEGORY
        svCover = rs8!LPRODUCTNAME
        svProviderType = rs8!PROVIDERCATEGORY
        
        If IsNull(rs8!ITEMNRDESC) Then
            svItemNr = ""
        Else
            svItemNr = rs8!ITEMNRDESC
        End If
        
        rs8.MoveNext
    Loop
    rs8.Close
    Print #nFile1, "function update_item_no(){"
    Print #nFile1, " document.mainform.item_no.options.length=0"
    Print #nFile1, " document.mainform.item_no.options[0]=new Option(""--Select Item Number--"", """", false, false)"
    Print #nFile1, " document.mainform.item_no.selectedIndex = 0"
    
    Set rs9 = dbs.OpenRecordset("SELECT RowNumber, TODATE, SSTATENAME, SERVICECATEGORY, LPRODUCTNAME, LPROVIDERTYPE, PROVIDERCATEGORY, Deleted FROM TEMP_EXF3 ORDER BY RowNumber, TODATE", dbOpenForwardOnly)
    
    Do While Not rs9.EOF
        intI = intI + 1
        If CStr(rs9!TODATE) <> "" And rs9!SSTATENAME <> "" And rs9!SERVICECATEGORY <> "" And rs9!LPRODUCTNAME <> "" And rs9!PROVIDERCATEGORY <> "" And rs9!Deleted = "N" Then
            If intI > 1 And Replace(RTrim(arrItemNr(intI)), SearchChar, "", 1) <> "" Then
                Print #nFile1, "}"
            End If
            strYear = rs9!TODATE
            strStates = rs9!SSTATENAME
            strModality = rs9!SERVICECATEGORY
            strCover = rs9!LPRODUCTNAME
            strProviderType = rs9!PROVIDERCATEGORY
            strItemNr = arrItemNr(intI)
            If Replace(RTrim(strItemNr), SearchChar, "", 1) <> "" Then
                Print #nFile1, "if (document.mainform.year.options[document.mainform.year.selectedIndex].value == """ & strYear & """"
                Print #nFile1, " && document.mainform.state.options[document.mainform.state.selectedIndex].value == """ & strStates & """"
                Print #nFile1, " && document.mainform.modality.options[document.mainform.modality.selectedIndex].value == """ & strModality & """"
                Print #nFile1, " && document.mainform.cover.options[document.mainform.cover.selectedIndex].value == """ & strCover & """"
                Print #nFile1, " && document.mainform.provider_type.options[document.mainform.provider_type.selectedIndex].value == """ & strProviderType & """"
                For intX = 0 To UBound(arrItemNr)
                  If intI <> intX And arrItemNr(intX) <> "" Then
                        If Replace(RTrim(strItemNr), SearchChar, "", 1) = Replace(RTrim(arrItemNr(intX)), SearchChar, "", 1) Then
                            Set rs0 = dbs.OpenRecordset("SELECT * FROM TEMP_EXF3 WHERE RowNumber = " & intX)
                            If Not rs0.EOF Then
                                Print #nFile1, "|| document.mainform.year.options[document.mainform.year.selectedIndex].value == """ & rs0!TODATE & """"
                                Print #nFile1, " && document.mainform.state.options[document.mainform.state.selectedIndex].value == """ & rs0!SSTATENAME & """"
                                Print #nFile1, " && document.mainform.modality.options[document.mainform.modality.selectedIndex].value == """ & rs0!SERVICECATEGORY & """"
                                Print #nFile1, " && document.mainform.cover.options[document.mainform.cover.selectedIndex].value == """ & rs0!LPRODUCTNAME & """"
                                Print #nFile1, " && document.mainform.provider_type.options[document.mainform.provider_type.selectedIndex].value == """ & rs0!PROVIDERCATEGORY & """"
                            End If
                            rs0.Close
                            arrItemNr(intX) = ""
                            DoCmd.RunSQL "UPDATE TEMP_EXF3 SET Deleted = ""Y"" WHERE ROWNUMBER = " & rs9!RowNumber
                        End If
                  End If
                Next
            Print #nFile1, ") {"
           End If
            DoCmd.RunSQL "UPDATE TEMP_EXF3 SET Deleted = ""Y"" WHERE ROWNUMBER = " & rs9!RowNumber
            svIndex = 0
            StartPos = 1
            SearchPos = InStr(StartPos, strItemNr, SearchChar, 1)
            If SearchPos <> 0 Then
                Do Until SearchPos = 0
                TempItemNr = Mid(strItemNr, StartPos, SearchPos - StartPos)
                        svIndex = svIndex + 1
                        If TempItemNr <> "" And TempItemNr <> "," Then
                            TempItemNrDesc = Mid(TempItemNr, 1, 28) & "..."
                            Print #nFile1, "document.mainform.item_no.options[" & svIndex & "]=new Option(""" & TempItemNrDesc & """,""" & TempItemNrDesc & """, true, false)"
                        End If
                        StartPos = SearchPos + 1
                        SearchPos = 0
                        SearchPos = InStr(StartPos, strItemNr, SearchChar, 1)
                Loop
            Else
                svIndex = 1
                If Replace(RTrim(strItemNr), SearchChar, "", 1) <> "" Then
                    strItemNrDesc = Mid(strItemNr, 1, 28) & "..."
                    Print #nFile1, "document.mainform.item_no.options[" & svIndex & "]=new Option(""" & strItemNrDesc & """,""" & strItemNrDesc & """, true, false)"
                End If
            End If
        End If
    rs9.MoveNext
    Loop
    rs9.Close
    Print #nFile1, " }"
    Print #nFile1, "}"
End Sub

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[formerTexan]

Well, besides the array (which off the top of my head should be about 26 megs with data), you also have a large recordset open concurrently. Perhaps you can reduce some of the memory overhead by reducing the recordset size. Plus whatever the current application is sucking up.

Might it be feasible to create another application with only your essential code module and a "temp" table. Export to the temp table only the field/data you will need for loading into the array. Base the recordset (rs8) on the temp table. Then close the original database and run your code from the new application. Smaller application, smaller recordset at crunch time.

As suggested earlier, try using GetRows for loading the array. It won't shrink the array, but may be faster. Also since you are using a local table, declaring the recordsets as DAO should speed things up bit. And using Len("string") <> 0 for empty string comparisons rather than "string" <> "" will be substantially faster.

Cheers,
Bill

 

[jrbarnett]

A few more pointers:

Dim arrCounter, svIndex, ItemNrCounter, intX, intI As Long

will declare intI as Long, but all other variables will be declared as Variants (arrCounter, svIndex, ItemNrCounter, IntX). Assuming that this is what you want for intX (and others) then put " As Long" after each variable name.


This part:
HAVING (((TODATE) = #12/31/9999#))

A having clause is evalated after data retrieval; if you replace this with "WHERE" then it will only retrieve those that meet the criteria and process them.
As there is no group by statement, this will not affect the retrieved data but may run more quickly.

 

[kjv1611]

I may have already missed the "how to" in this thread on this point, but it's got me curious:

How can you figure out how much memory each part of your code/application/database takes up? Is there some sort of log report you can generate? Or is there some calculations you keep handy to do all of that? I know that there is bits and bytes and all, and that different variable and field types use differing amounts of memory (the bits and bytes). So, are the mentions of how many megabytes being used refer to such a calculation, or is there some easier/simpler way of keeping up with all of that?

Thanks for any thoughts - I'm just curious at this point.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Crowley16]

you can find out how much memory access is using by going to the task manager, however to find out what specific things in your code is using, you're gonna have to do a memory dump and analyse that, on-one really does this...

you can estimate the amount of memory an array uses just by multiplying the number of objects in the array by the size of each object, e.g. an array of 10,000 5byte strings will be 10000 * 5 = 50Kb of memory roughly... There will always be overhead to that but it will be different for each application...

--------------------
Procrastinate Now!

 

[Zeroanarchy]

jrbarnett
Thanks for pointing out the "HAVING (((TODATE) = #12/31/9999#))" I should remove it from the SQL, as all the records in that table are set to that date. A previous step only adds those records that criteria to the temp table "TEMP_PDLD".


formerTexan
Unfortunately I can not limit the number of records in the temp table, but what I may be able to do, is sum the recordset, then divided it by say four, and loop the records in four loops instead of all at once. I assume the process will take longer but if it prevents access from falling over then it will be worth it.

kjv1611
What Crowley16 said was right. What I actually do is place break points in my code, then follow the process via the task manager to assess which part of my code is chewing through memory.

Thanks all I will keep at it.


Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.