469,266 Members | 1,880 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

system resource exceded

232 100+
Expand|Select|Wrap|Line Numbers
  1. Function ASD()
  2. DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000
  3. CurrentDb.Execute ("ALTER TABLE EMP ADD COLUMN SEQ AUTOINCREMENT")
  4. CurrentDb.Execute ("UPDATE EMP SET SEQ=SEQ+100000")
  5. CurrentDb.Execute ("UPDATE EMP SET SEQ=SEQ+(((SEQ MOD 7)+1)*1000000)")
  6. End Function
it is to generate unique sequence no but the code gives error system resource exceded
kindly help to correct it
Sep 11 '13 #1
13 12605
jimatqsi
1,260 Expert 1GB
Are you calling this function from within a loop? What do you mean by "unique sequence?" Are you trying to get a random number? Your resulting sequence is going to be increasing by at least 1.1 million every time you run this. Is that what you want?

Jim
Sep 11 '13 #2
zmbd
5,400 Expert Mod 4TB
1.5 million file locks... why?

DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000
You should only call this option ONCE, preferably in the main code module, not every time you execute the function and you should release these resources in the main code.

Also, there is an error NUMBER associated with that error text. Please post BOTH number and text for errors. Many of the errors share the same or similuar text so the number is also required.
Sep 11 '13 #3
Seth Schrock
2,962 Expert 2GB
Possibly using a variable to store the CurrentDb instead of calling it three times would also help.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Set db = CurrentDb
  3.  
  4. db.Execute ...
  5. db.Execute ...
  6. db.Execute ...
  7.  
  8. Set db = Nothing
I seem to remember that over use of system resources was part of the reason for using the variable instead of just calling the function each time.
Sep 11 '13 #4
zmbd
5,400 Expert Mod 4TB
Seth: I seem to remember that over use of system resources was part of the reason for using the variable instead of just calling the function each time.
Seth you are correct, each call doesn't necessarily return the same pointer so each call can set a new pointer; however, once the scope of the function ends, the resources theoretically release.

We hit on that topic sometime in the first few threads you posted - doesn't seem that long ago.
The old age is catching up with you!
Sep 11 '13 #5
kkshansid
232 100+
its runtime error 3035
system resource exceeded
i also tried method 2 of the link after commenting first line of code
http://support.microsoft.com/kb/815281/en-us
i am also a little bit confused that just by typing the code in immediate window and pressing enter key how come the code run

same error again

it is also to inform that it is the main function of a small program which works fine for 17145 records but now i have to run the same code on 2.5 lakh records
Sep 12 '13 #6
zmbd
5,400 Expert Mod 4TB
- What version of Access are you using?
- You will still need to have the DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000 called to adjust the file locking. Just move it to a point where it is called only once.
- You shouldn't need to lock 1.5 MILLION records when you are only handling 250,000 records.
- Please do not use regional units such as "lakh", while many in and around south-east Asia and Persia may recognize this to mean 100,000 the rest of the world may not as there is no equivalent in the European numbering system.
- 3035 error... if this is a split database, you may be exceeding the capacity of the server to handle the file locks, especially if it is old, slow, or on a bad communications line. Change your locks back down to DAO.DBEngine.SetOption dbMaxLocksPerFile, 300000 , next open and keep open a record set to a dummy record set, use a table with a single record if needed - you won’t need any information from this record set. This will keep your connection available. Remember to release the dummy connection once the transactions or the session has completed.
-- related to above, if this is a split database, then what are the specifications for the server?
- Please post the entire code where you calling this function from. There are more than likely other issues therein that are also contributing to the problem.
- Please answer jimatqsi's question... Post#2
Sep 12 '13 #7
kkshansid
232 100+
Access 2007 on window 7
this is the main function of the program called only once
yes i want to get unique sequence number
Sep 17 '13 #8
zmbd
5,400 Expert Mod 4TB
this is the main function of the program called only once
Then this should typically be a "Sub" not a function as you are not returning any values.

Did you read thru the remainder of my last post? Did you try reducing the file locks? Did you try the "keep-open" method?

It might help to understand why you are not using an autonumber field and adding records as needed instead of a bulk addition of what appears to be a hundred-thousand blank records.
Sep 17 '13 #9
kkshansid
232 100+
if i make autonumber field it still gives same error
i tried by reducing locks
performing same activity on newly created file
i also tried method 1 of the link by changing registry entry
http://support.microsoft.com/kb/815281/en-us
still same error "system resource exceeded"
kindly explain keep open method in details i will also try that
Sep 23 '13 #10
zmbd
5,400 Expert Mod 4TB
kkshansid
(K): if i make autonumber field it still gives same error (...)
If you still trying to add the same number of records in one go then that doesn't surprise me. The thing with the autonumber is that you should not need to pre-create the records. If you do, then there may be some errors in the table design.
(K): i tried by reducing locks (...)
To what level?
(K): i also tried method 1 of the link by changing (...)
The registry method does the same thing as the method you tried in code; however, the registry method is a more permanent change whereas the code method is only valid while the project is running (and I think within scope too). This is one reason why we suggested pulling the code that set the record lock upperlimit out of the called sub/function and placing it within the calling sub/function code.
The one thing this does help support is that you may simply be exceeding the capacity of the infrastructure you are working with to handle the record writes.
(K): kindly explain keep open method in details (...)
Asked and answered in post#7. Make a single record table, need only have one field, say numeric long (or even byte - for that fact it could be a Boolean field, it doesn't matter) with the number zero for the value. In VBA prior to starting your massive record push, open a recordset to this "dummy" table, keep it open. What I've done for this is to create a form with the dummy table as the record source and records locked with two controls, one hidden and bound to the single record and the other visible. I then use the visible control on this open form as a "message" board for user feed-back. I then close this form once the transactions are completed. However, I haven't had to use this method in over 10 years for two reasons:
1) IT upgraded all of the servers and switches.
2) I learned better database design methods and realized that my database was horribly designed. In fact, I discovered that most of the Databases we were using were poorly design attempts at using Access like we had Lotus123 and Excel.

Major drawback with this method is that once the dummy table is locked like this, only ONE person can have the table locked at a time which defeats the split-database multi-user concept. You also need to put code in place to check for and handle the table being locked should another client attempt to place a lock on the record at the same time or after the first lock is established.
Sep 23 '13 #11
kkshansid
232 100+
Expand|Select|Wrap|Line Numbers
  1. Function SEQGEN()
  2. CurrentDb.Execute ("ALTER TABLE tab1 ADD COLUMN SEQ int")
  3. CNT = 0
  4. Dim rs As ADODB.Recordset
  5. Dim strSQL As String
  6. Set rs = New ADODB.Recordset
  7. strSQL = "SELECT * from tab1"
  8. rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  9. Do Until rs.EOF
  10. CNT = CNT + 1
  11.     With rs
  12.         !SEQ = CNT
  13.     .Update
  14.     End With
  15. rs.MoveNext
  16. Loop
  17. rs.Close
  18. Set rs = Nothing
  19. MsgBox "FILE PRINTED SUCCESSFULLY"
  20. End Function
after adding 337599 error comes "can not open database it may not be database that your database recognise or the file may be corrupt"
even the simple code is not running kindly tell me if access is not gud for large database
Sep 26 '13 #12
kkshansid
232 100+
even the simple code is not running kindly tell me if access is not gud for large database
Sep 26 '13 #13
zmbd
5,400 Expert Mod 4TB
I think you are not telling us something about the design of your database. I asked about this back in post#7
Is this a split database?

Access2007(32Bit) easily handles 2GB files… Access 2007 specifications

As far as your simple code – although a bit clunky, it ran just fine on my test db… added 150K records in the loop… I tested it twice, once on the local PC and again over my test network on a split database using only the default record locks etc..

It would be better if you start from the beginning here and tell us something about your project. The code you are currently writing... is... to put it kindly... yuuck. If we knew what you were trying to accomplish, other than overloading your infrastructure, then we may be able to offer a better solution.

And before I can help you with any more code you absolutely must read thru the following link, set the "option explicit" ,( you’ll have to add it by hand to the current ones), in all of your code modules, forms, class modules, etc... and then successfully complete a debug/compile. > Before Posting (VBA or SQL) Code – NOTE: You will have to repeat the compile step multiple times to find and fix all of the syntax and usage errors in the code. The compiler stops on the very first error it encounters.

One last thing, why are you using "functions" when you are not returning any values from them to the calling code. Best practice is to use "sub" and then "call" the "sub" from within the main routine.
Sep 26 '13 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by S.Chang | last post: by
1 post views Thread by Roberto Ortolano | last post: by
2 posts views Thread by mark | r | last post: by
10 posts views Thread by Niall | last post: by
4 posts views Thread by Daniel N | last post: by
3 posts views Thread by Martin Pöpping | last post: by
reply views Thread by sivaji | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.