1

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!

Erik A
  • 31,639
  • 12
  • 42
  • 67
Project_H
  • 59
  • 2
  • 10
  • try to fix this problem by increasing the MaxLocksPerFile registry entry which could be accomplished in VBA. To do this I inserted the following line of code in the “form load” procedure of the form that was getting the error messages: DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000 – HaveNoDisplayName May 15 '15 at 15:27
  • Thanks, I tried that already and it didn't work. – Project_H May 15 '15 at 15:43
  • Are all of the workstations identical? Does the one that fails have less RAM or disk space than the others? – kismert May 15 '15 at 22:19
  • They are all relatively identical. Windows 7 32-bit, 4 GB of RAM... – Project_H May 18 '15 at 14:24
  • Upgraded to Access 2013 64-bit and 8 GB RAM, upped MaxLocksPerFile and MaxBufferSize gradually to &ha0000000 (2,684,354,560). Nothing works. Damn it Microsoft! – Project_H Jun 08 '15 at 15:43

1 Answers1

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.

kismert
  • 1,662
  • 1
  • 13
  • 19