system resource exceded

[kkshansid]

Function ASD()
DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000
CurrentDb.Execute ("ALTER TABLE EMP ADD COLUMN SEQ AUTOINCREMENT")
CurrentDb.Execute ("UPDATE EMP SET SEQ=SEQ+100000")
CurrentDb.Execute ("UPDATE EMP SET SEQ=SEQ+(((SEQ MOD 7)+1)*1000000)")
End Function

it is to generate unique sequence no but the code gives error system resource exceded
kindly help to correct it

[jimatqsi]

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

[zmbd]

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.

[Seth Schrock]

Possibly using a variable to store the CurrentDb instead of calling it three times would also help.

Dim db As DAO.Database
Set db = CurrentDb
 
db.Execute ...
db.Execute ...
db.Execute ...
 
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.

[zmbd]

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!

[kkshansid]

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

[zmbd]

- 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

[kkshansid]

Access 2007 on window 7
this is the main function of the program called only once
yes i want to get unique sequence number

[zmbd]

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.

[kkshansid]

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

[zmbd]

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.

[kkshansid]

Function SEQGEN()
CurrentDb.Execute ("ALTER TABLE tab1 ADD COLUMN SEQ int")
CNT = 0
Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = "SELECT * from tab1"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
CNT = CNT + 1
    With rs
        !SEQ = CNT
    .Update
    End With
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
MsgBox "FILE PRINTED SUCCESSFULLY"
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

[kkshansid]

even the simple code is not running kindly tell me if access is not gud for large database

[zmbd]

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.