Access - System Resource Exceeded

[MoBetter]

I am currently running Microsoft XP with 1 GB of RAM and the Virtual Memory is set:

Initial Size (MB): 1524
Maximum Size (MB): 3048

I have a 75 GB hard drive:

34.6 GB Used
39.7 GB Free

I created a Microsoft Office Professional 2003 Access db that grew and I compacted it, so the size is now 1,360 KB (1.32 MB)...; however, when I tried to execute a query I still receive a message that indicates "System Resource Exceeded".

Is there a limit on the size a db can grow or is there something obviously or suspiciously wrong?

Any assistance or comments on this...?

Thanks

 

[JoeAtWork]

Your database is tiny (the limit is 2GB), so that is not the problem. Next step: show us your query.

 

[lameid]

Try the solution in this article...

http://support.microsoft.com/kb/815281

I find in complex queries the defaul maxlocksperfile setting often causes problem. First thing I always try. If it takes longer to fail after increasing, it is working but you have not set it large enough.

 

[MoBetter]

Thanks JoeAtWork... I went with lameid idea and implemented the following code to increase the db lock while it is opened:

Sub LargeUpdate()
On Error GoTo LargeUpdate_Error
Dim db As DAO.Database, ws As DAO.Workspace

' Set MaxLocksPerFile.
DBEngine.SetOption dbMaxLocksPerFile, 429496777

Set db = CurrentDb
Set ws = Workspaces(0)

' Perform the update.
ws.BeginTrans
db.Execute "UPDATE TEMP SET field1 = 'Updated field'", _
dbFailOnError


ws.CommitTrans

db.Close
MsgBox "Done!"
Exit Sub

LargeUpdate_Error:
MsgBox Err & " " & Error
ws.Rollback
MsgBox "Operation Failed - Update Canceled"
End Sub

-----
However, an error message appears:

3061 Too few parameters. Expected 1.

Operation Failed. - Update Canceled

Is there something that I am leaving out in the code since it appears the code is looking for something extra.

Thanks

 

[lameid]

Nothing is jumping out at me to cause that error.

It is likely the SQL statement you are using with your Execute statement. Try pasting the SQL string into a query to check its syntax.

Try commenting out the On Error line and debuging to see what line it is failing on. That is a key piece of information.