Access 2010 Error 3035 System Resources Exceeded on Update Query

Question

I've got a routine that imports and performs some transformations on a 481 MB text file.

The routine crashes on this code block for one workstation (but works fine on our other workstations):

' All of the eleven-digit telephone numbers encountered end in 0. If we get rid of the ending 0,  
' we may end up with a valid telephone number.  
SQLString = "UPDATE " & sTableName & _  
            " SET [ServAddrPhone] = Int([ServAddrPhone]/10) WHERE [ServAddrPhone] >9999999999 ; "
RoboCallDB.Execute SQLString, dbFailOnError

[ServAddrPhone] is a double. The code block scans through some 800,000 records looking for telephone numbers greater than 10-digits, and generally finds about 3,000 records eligible for an update.

How can this code be optimized to avoid Error 3035? Thanks!

Answer 1

OK, assuming that the machines all have roughly the same resources, I would try:

1. Split the 480 MB text file into two 240 MB pieces.
2. See if your problem machine can handle the smaller chunks.

If this works, I would just write code to split this file up, and import each piece separately.

Note: be sure to exit and re-enter Access before loading each piece.

I had this exact error on a large ETL process I was running, and I wound up splitting the process into smaller chunks to solve this issue.

This leads me to think that 'Local Access Application Resources' are what is really being exceeded, not 'System Resources', as the error claims.

I know that this advice might be a pain for you to implement, but sooner or later that text file will grow large enough to choke any of your computers.