Running out of Memory in a long VBA routine

I have an Access table with more than 10 million records. I have been able to run many queries on the table. Now I want to update a field in the table based on information in a several hundred-record lookup table. I'm using a VBA routine instead of a query, and it runs well for several hundred thousand records. But then I get a message that the table is corrupted or may not be an Access table; I believe that the real problem is that I've run out of some type of memory to continue the routine. Does anyone have any suggestions?

Here is the code:

Public Sub AddPowerSourceToTable()

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Const cstrPower             As String = "strPower"
    Const cstrPrefix            As String = "strPrefix"
    Const cstrMdlNmbr           As String = "strModelNmbr"

    '   *****   The lookup table with the data for use in the updat
    Const cstrSrcTbl            As String = _
    '   *****   The table to be updated
    Const cstrRsltsTbl          As String = _

    Dim adoMgt                  As cADOrstManagement
    Dim rstSrc                  As ADODB.Recordset
    Dim rstRslts                As ADODB.Recordset

    Dim strT                    As String
    Dim strF                    As String
    Dim lngC                    As Long
    '   *****   This is a class module that simplifies opening of _
    Set adoMgt = New cADOrstManagement
    Set rstSrc = adoMgt.OpenADORecordset(cstrSrcTbl, True)
    Set rstRslts = adoMgt.OpenADORecordset(strSQL) 'cstrRsltsTbl, True)    '

    With rstRslts

        lngC = 1

        '   *****   For each record in the results table
        Do While Not .EOF

            '   *****   Get the prefix of the model number
            strF = Left(.Fields(cstrMdlNmbr), 4)

            '   *****   Search for that prefix in the Power Source table
            With rstSrc
                '   *****   This uses the open class module to create _
                            a valid search string
                strT = adoMgt.ADOFindString(cstrPrefix, strF)

                .Find strT

                '   *****   If there's no find, then simiply add dummy data
                If .EOF Then

                    strT = "x"

                    '   *****   Add the information from the lookup table
                    strT = .Fields(cstrPower)

                End If

            End With

            '   *****   Add the information to the results table
            .Fields(cstrPower) = strT



            '   *****   a counter to monitor progress, as necessary
            lngC = lngC + 1


    End With

    Set rstSrc = Nothing
    Set adoMgt = Nothing

    'TVCodeTools ErrorHandlerStart
    Debug.Print "Power Update Routine Exited Normally."
    Exit Sub

    MsgBox Err.Description, vbCritical, "Module1.AddPowerSourceToTable"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Sub

I'll appreciate any suggestions.
Who is Participating?
IrogSintaConnect With a Mentor Commented:
I agree with the other experts here about using a SQL Update.  It would probably look something like this.  I'm not SQL savvy so hopefully another Expert here can modify this to account for updating strPower to "x" when the prefix is not found in the tblSrc.

UPDATE tbl_Rslts, tbl_Src SET tbl_Rslts.[strPower] = tbl_Src.[strPower]
WHERE LEFT(tbl_Rslts.[strModelNmbr], 4) = tbl_Src.[strPrefix]
Curious why you don't think your table is corrupt as the message indicates?

Have you ruled this out by compact/repairing your database, copying the records to a new table..etc...?
PelegrinusAuthor Commented:
Yes. I've done both compacting/repairing and copying the records to a new table. Also, other queries run through the entire table without problems. Although I admit that I'm guessing it's a memory problem, I have monitored memory usage during the running of the routine, and the memory goes down continually as the routine runs and approaches zero.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

NorieVBA ExpertCommented:
Couldn't you use an UPDATE query?
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Couple comments:

1. I'd look at the class module for finding the string - if it's not cleaning up properly, your not going to last long.  Now sure why your bothering with the overhead is instantiating a class either.

2. Why do you feel you need all the DoEvents?   Your just making the operation take longer.  If you want the db engine to catch up, you need to be issuing a dbEngine.Idle, not a DoEvents.

3. Check that the DB is not actually running out of space.  Updating the field may cause the records to expand.

 Depending on the search string logic, you might have been able to do this faster with straight SQL and a join between the tables.

 This is BTW a JET backend correct?

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<No Points wanted>

I am with imnorie, ...unless you update is extremely complex, an SQL UPDATE will run much faster... and use much less system resources than using ADO recordset code...

(I am also seeing some "DoEvents" in your code, this may indicate timing issues already...)

Again, SQL will access the table directly, thus be faster.

PelegrinusAuthor Commented:
I am going to be traveling for the next nine days but will return to this after that. I hope to give the SQL query a try. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.