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 = _
                "tbl_Src"
    '   *****   The table to be updated
    Const cstrRsltsTbl          As String = _
                "tbl_Rslts"

    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 _
                adodb.recordsets
    Set adoMgt = New cADOrstManagement
    Set rstSrc = adoMgt.OpenADORecordset(cstrSrcTbl, True)
DoEvents
    Set rstRslts = adoMgt.OpenADORecordset(strSQL) 'cstrRsltsTbl, True)    '
DoEvents

    With rstRslts

        .MoveFirst
       
        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
DoEvents
                .MoveFirst
               
                '   *****   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"

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

                End If

            End With

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

            .Update

            DoEvents

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

        Loop

    End With

    Set rstSrc = Nothing
    Set adoMgt = Nothing

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

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

End Sub


I'll appreciate any suggestions.
PelegrinusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
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...?
0
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.
0
NorieAnalyst Assistant Commented:
Couldn't you use an UPDATE query?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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?

Jim.
0
Jeffrey CoachmanMIS 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.

JeffCoachman
0
IrogSintaCommented:
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]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.