System Resources Exceeded error

Thanks Jim, I'll give that a try right now.
I'll also read up on the UseTransactions property.
I haven't heard of it before.

Jim (or anyone) ...

I added the line:

DAO.DBEngine.SetOption dbmaxlocksperfile,200000

and received a procedure too large message.
I deleted all unnecessary comments and unnecessary code and I'm still getting the too large error.

I also moved a large subroutine into a module and still get the error.

Any suggestions?

I am running many queries... I use the docmd.runsql("sqltext") option.

Any suggestions?

Making programs but have a new problem
The dbMaxLocskperfile options seems to work when the code appears after the set odb=currentrecordset() statement.

now I'm getting a new error:

odbc--update on a linked table selectedparts failed

Any item what this means?

I believe this may have worked.  Initially I was receiving a procedure too large error after adding the dbmaxlooksperfile code, however, when I added the line after the database object was defined it seems to work!  Thanks.

Great...so your set then?

Jim.

Actually, no.

I have no longer receiving the system resource error, but now I'm getting the following error:

odbc--update on a linked table selectedparts failed

The error occurs after about 10000 rows are created in the SelectedParts table.
At that point, every time the .update line is executed, the code produces this error.

If I leave the debug window, and click on the table in the immediate window, I have no trouble accessing the table so, so its not a network issue.  I'm wondering if something is timing out.  I wasn't sure what the protocol was when a second problem is encountered after one problem is solved, so I opened another question to ask for help on this problem.

Any ideas?

https://www.experts-exchange.com/questions/28644305/Help-needed-with-odbc-update-on-a-linked-table-selectedparts-failed.html

<<Any ideas?>>

 Are you getting the generic 3146 error?   If so, make sure you enumerate the Error collection to get additional errors the drive might be returning:

How To Get More Information on the ODBC Call Failed Error
http://support.microsoft.com/en-us/kb/161288

Jim.

Jim, I have a real mess now.

I added the code from the link but started getting a user defined variable error at the  line:

Dim MyError As rdoError

I made a couple of other minor changes to the file as well, and no am receiving a procedure too big message.
I moved a large subroutine to a module, and deleted a bunch of comments, and still get the procedure too big message.

In other words, nothing is working at all now.  Perhaps moving code to a module does not change the procedure size?
I must be right on the edge of the size limit?

<<Dim MyError As rdoError>>

 RDO is long gone (it's an old article).   All you needed to add to your existing error handling is:

      Dim MyError As Error
      MsgBox Errors.Count
      For Each MyError In DBEngine.Errors
        With MyError
          MsgBox .Number & " " & .Description
        End With
      Next MyError

      Dim MyError As Error
      MsgBox Errors.Count
      For Each MyError In DAO.Errors
        With MyError
          MsgBox .Number & " " & .Description
        End With
      Next MyError

 Both should give you the same thing.

<<and still get the procedure too big message.>>

 compile and make sure you have no errors (like possibly a missing end function or sub).

Jim.

Jim.

Perfect, I'll give that a try.

I moved another routine to a module and the too large error is gone.
I'm trying the code now.  Thanks

Took me a while to get this working (long story)...

so after running for two or three hours, the new code produce the message:

[Microsoft][ODBC SQL Server Driver]Timeout expired

Any ideas?

<<Any ideas? >>

  ODBC timeout by default is 60 seconds.  When executing a query, it may take far longer then that.  On a querydef, you can set the timeout to how long you think it will take (ODBC timeout property), including 0, which means no timeout.

 That's all you need.

Jim.