vba - "System resource exceeded" when updating recordset, and possibly at other points -

i'm experiencing intermittent issue application that's excel 2010 front-end, access 2010 end. it's in use 5-10 users simultaneously. recently, users have started intermittently receiving following error:

run-time error '3035': system resource exceeded.

sometimes debug button grayed out can't jump code caused error, when it's available click, takes me following code:

 'open connection end db  set db = opendatabase(dbpath)   'open recordset of table  set rs = db.openrecordset(tbl)   'loop through rows in 2d array  = fr lr   rs.addnew    'loop through columns of 2d array    j = 1 lc     'set values various fields in new record, using values array    next   rs.update  next 

here, rs.update marked line that's causing error.

what's odd problem comes , goes; users repeatedly receive when attempting submit data set, then, several hours later, when try submit same data set again, operation succeeds without error. it's perplexing debug button available , isn't.

one issue might size of access end; it's ~650 mb, , didn't start getting these messages until grew around 600 mb.

any ideas causing this? various google hits indicate problem happens when join query has many fields, recordset of table, not join query.

ahh, methink 1 of strange errors pop-up when write lot backend database can't keep management of lock file.

the solution make sure keep connection open back-end database each of client , hold onto connection until close client.
open recordset table (say dummy table 1 record), , keep recordset open until close application.

resource-wise, keeping connection open have no detrimental effect on performance or memory consumption, ensure lock file not continuously created/deleted every time connection open closed.

keeping connection open substantially increase performance of data access.

edit:

you should more explicit when using recordsets , specify mode of operation need:

set rs = db.openrecordset(tbl, dbopentable, dbfailonerror) 

or, faster if appending data:

set rs = db.openrecordset(tbl, dbopentable, dbappendonly + dbfailonerror) 

also make absolutely sure close recordset once you're finished appending data!:

    set rs = db.openrecordset(tbl, dbopentable, dbappendonly + dbfailonerror)     rs         'loop through rows in 2d array         = fr lr            .addnew               'loop through columns of 2d array               j = 1 lc                  'set values various fields in new record,                   'using values array               next            .update         next         .close     end     set rs = nothing