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()
On Error GoTo PROC_ERR
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) '
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
' ***** This uses the open class module to create _
a valid search string
strT = adoMgt.ADOFindString(cstrPrefix, strF)
' ***** 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)
' ***** Add the information to the results table
.Fields(cstrPower) = strT
' ***** a counter to monitor progress, as necessary
lngC = lngC + 1
Set rstSrc = Nothing
Set adoMgt = Nothing
Debug.Print "Power Update Routine Exited Normally."
MsgBox Err.Description, vbCritical, "Module1.AddPowerSourceToTable"
I'll appreciate any suggestions.