System Resources Exceeded error

I need to get past a system resource error.
I'm using Access 2k on a machine with 3GB and an XP SP3 workstation.

Initially I was exceeding the 2GB space limit of the backend database, so I upsized to MS SQLSERVER and not receive a System Resource Exceeded message.

At the time that the code fails it is looping through 60K records, and saving data into a MS SQLSERVER backend table.

Am I running out of memory, hard drive space, variable space, or something else?
From performance tab.  Screen shot take immediately after I broke out from error...
Commit Charge
Total 631856
Limit 2727172
Peak 768776

Physical Memory 2095084
Available 1161620
System Cache 917644
pcalabriaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Locks probably.

Not sure what your doing in the code, but try adding:

DAO.DBEngine.SetOption dbmaxlocksperfile,200000

to the code at the start.  See if that clears it up.  Also, if you can run "action" queries with the UseTransactions property set to no, you'll avoid running out of resources for locking entirely.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcalabriaAuthor Commented:
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.
pcalabriaAuthor Commented:
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pcalabriaAuthor Commented:
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?
pcalabriaAuthor Commented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Great...so your set then?

Jim.
pcalabriaAuthor Commented:
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?

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28644305.html
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
pcalabriaAuthor Commented:
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?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
pcalabriaAuthor Commented:
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
pcalabriaAuthor Commented:
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?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.