Error 3239 - Too many active users (AC2007)

AOB

Registered User.
Local time
Today, 03:14
Joined
Sep 26, 2012
Messages
637
Hi guys,

I have a 'standard' Access application in place - split FE/BE, BE on a shared network drive, individual FE's on each user's local machine. Has been operational for several months with no real issues (i.e. nothing I haven't been able to isolate in code and correct to prevent future recurrence)

This morning, I checked the error log (as I do most days, just to see if anything unusual has cropped up) and found one of my users hit an Error 3239 ("Too many active users") when opening the FE

(The error handler / logger tells me the sub/function and module where the error occurred, and on this occasion it was the function to relink the tables using the drive mapping specific to the local machine. This is only called once, at startup, to reduce latency in the data connections)

I've never seen this error occur before, and I doubt very much it is actually due to there being too many active users (at the time she tried to open the FE, there would have been 9 other people connected; I have, in the past, seen up to 14 or 15 people 'active' at any given time, in the sense that they had the application open, and it has never been an issue?)

Is this something I should worry about (and how would I go about handling such a scenario should it arise again) or could this just be a freak occurrence due to network issues (out of my control anyway) or a rare concurrency event that is unlikely to arise very often?

Thanks in advance!

Al
 
That's most probably a transfer error: don't forget that parity is not checked since Jurassic park. So when something like that occurs, well too bad for the user...
 
Last edited:
Access allows 255 concurrent users - 255 for read only access, much less if most users are manipulating data.

I can think of a couple of things:

1. Is there a possibility that some of your users can locate and open the backend? Worth investigating!
2. Does each user have the right file/folder permissions on the backend to delete a file? They would require this to be able to delete the lock file after the last user logs out.
3. Do you have code that remotely opens the db from within another application (e.g. Excel)? A proper cleanup is required, i.e. closing and nullifying the db object.
4. Recordset code within the Access app that isn't properly closed and nullified.
5. At the time of the error, did you or someone have the backend db opened exclusively to (perhaps) update one or more of the tables?

Also, how many users do you have in total? If the number of users is different from the number of deployed front ends, how many deployed front ends are there?

You could investigate kicking out your users at the end of each working day but let's focus on the points raised above.
 
I agree with vbaInet but have to add one more question: Are your queries set up for optimistic or pessimistic locking?
 
1. Is there a possibility that some of your users can locate and open the backend? Worth investigating!

It is indeed possible, yes - but extremely unlikely. For the simple reason that everybody is far too busy to go trawling through the server looking for something that gives them no real benefit (they can get everything they need through the easily-available FE) Plus none of them would be very Access-knowledgable; I doubt many of them even realise there is a BE somewhere storing all the information (and wouldn't know what to do with it if they found it) So I'm fairly comfortable crossing this one off the list.

2. Does each user have the right file/folder permissions on the backend to delete a file? They would require this to be able to delete the lock file after the last user logs out.

Yes, they do. Including the user who had the problem.

3. Do you have code that remotely opens the db from within another application (e.g. Excel)? A proper cleanup is required, i.e. closing and nullifying the db object.

The db is opened via a shortcut on the desktop / Start menu which points to a .vbs file which basically checks the locally saved FE with the latest FE on the network, copies it over if necessary and launches it in Access. This is the only way anybody opens the db.

4. Recordset code within the Access app that isn't properly closed and nullified.

No recordset object used in the function which raised the error. There is a Database object, and a TableDef object, but they follow a convention I've stuck rigidly throughout all my code whereby any such objects are closed / set to nothing as part of the Exit process for any sub or function (so they are guaranteed to be nullified, even if the error handler is invoked at any stage)

5. At the time of the error, did you or someone have the backend db opened exclusively to (perhaps) update one or more of the tables?

No. As per point 1, I'm pretty certain I'm the only person who a) knows where the BE is and b) would have any desire to touch it. I developed some functionality for the rare occasions when BE updates are required, which locks FE users out (thus allowing me to open exclusively without fear of a rogue connection mid-update) - I certainly wouldn't have been messing with the BE without making use of said functionality. And I know for a fact I haven't made any BE updates in several weeks.

Are your queries set up for optimistic or pessimistic locking?

I've never really delved into this but I believe they are set up for optimistic locking? In the sense that in the 'saved' queries' property sheet, Record Locks is set to "No Locks" (am I looking at the right property?) I do have some more dynamic queries which are constructed in VBA and fired using .Execute; are such queries optimistic by default? I'm not sure (but would love to learn)

don't forget that parity is not checked since Jurassic park

Sorry, you lost me there mate?...

As an FYI, I checked my logs and a couple of other users were able to connect just minutes after my problem user suffered her error. And she herself was able to connect later that day without issue. So it does seem to have been an isolated incident (albeit one I would prefer to understand lest there is a darker issue in the background)

Thanks as always guys!
 
Whoops, missed the last two there vbaInet...

Also, how many users do you have in total? If the number of users is different from the number of deployed front ends, how many deployed front ends are there?

As of this morning, I have 24 'registered' users. Typically, less than half of these are connected at any given time. Some open the app in the morning, check/update what they need to check/update, and close it again. Others open it first thing on Monday morning and leave it open all week (our office runs an automatic shutdown/restart every weekend)

I can't tell how many deployed front ends there are - in theory, it should just be the same number as registered users. However, it could have been installed by somebody who has not registered, or one of my registered users could have switched PC's, and installed a second copy to their new workstation. But I've no way of knowing, short of going from PC to PC to see if I can find the FE on the local machine.

You could investigate kicking out your users at the end of each working day but let's focus on the points raised above

That's pretty much the logic I use as described in Q5 - I use a hidden form with a timer which allows me to remotely force all other users out and then block any new connections until such time as I am ready to allow them. I have it as a button on a form and only use it ad-hoc when I need to.
 
If your problem is helped by your "hidden form with timer to kick out users" then your problem is your users are not correctly exiting from the database and yet are not closing out their connections either. You have persistent connections, which is a bit perplexing because it tells me that you are violating one of those rules from the "Everything I Needed to Know I Learned in Kindergarten" book. "If you open it, close it."

For some reason, your connections that you establish from the FE files are not getting closed to the BE file, so the connections accumulate. The locks associated with those connections don't get cleared, which is probably how it knows you have too many users.

I would make it a point to have code in your database such that when it is about to be closed by a user that you intercept the event and close all recordsets to the BE before you dismiss the event. You say you do, but this leads me to a basic design question.

You DO force your users to enter through a switchboard or dispatcher form, don't you? And that form never closes until they exit, right? Because if you don't do it this way then you don't have a mechanism by which to trap that closure. And if so, that is your real problem. Your cleanup has to have a vehicle to drive it at user exit in order for an enforced connection termination to occur.
 
Thanks Doc Man

You DO force your users to enter through a switchboard or dispatcher form, don't you? And that form never closes until they exit, right?

Absolutely correct, I have a main switchboard form from which all other functionality is driven. That form is opened on startup every time (via an AutoExec) and remains open for the duration of a users connection.

What should I be adding to the "shutdown" procedure to ensure that all connections to the BE are most definitely terminated? (How do you close a Recordset object which is private to a specific form / module, from outside said form / module?)
 
(How do you close a Recordset object which is private to a specific form / module, from outside said form / module?)

The form will ALSO have a Close event and an Unload event that you can use profitably, so you should not HAVE to attempt a shutdown from outside the module. Because except in very rare and usually contrived cases, you CANNOT close the object from outside the class module.

Here is a rhetorical question: Since closing the class object makes its pointer variables meaningless, why would you ever WANT to keep the objects declared privately by that class module? You can never see them again after their pointers (in the private declaration areas) are invalidated. What good are they after that moment?

This is the problem: That recordset, to the best of my knowledge, is managed in memory but NOT in the memory occupied by the compiled code class module. There is a supporting secondary data structure in working memory (I believe in the user scratchpad memory pool). Some programming languages call this area the HEAP - as opposed to the STACK. In traditional programming languages, they are at opposite ends of the same user-private working memory area and grow towards each other. Calling a subroutine mucks the stack. Creating a NEW object mucks the heap.

If you exit the class module but do not close the recordset, it is true that the class module's content is gone, but it is not always true that the other part of the recordset is gone because, in the lingo of modern Object Oriented Languages, the true recordset structure does not have an automatic destructor.

When closing out a recordset whether in a general module or a class module, you always need two steps to assure proper closure and recovery.

Code:
recordset.Close
SET recordset = Nothing

That is the only true way (that I know of) to assure proper closure of a privately declared recordset (and, by extension, other objects of the same scope). Close the object and then dereference the pointer. Or exit Access entirely, or reboot your system. But if you didn't want to Exit or Reboot, then you have to close and dereference the object.

That will assure that there is no remnant that could make your system get clogged up with useless data structures. If you allow the class module to close when the form closes, you lose your pointers FOREVER and also lose the ability to manipulate the previously referenced objects after that closure.
 
Last edited:
Thanks for the clarity Doc Man, I appreciate it.

I guess my issue here is, that is precisely the methodology I employ throughout my code - be it specific to forms, or modules. If I instantiate a Recordset object (or indeed any object which could have a knock-on effect down the line if not properly nullified), I always follow that two-step process of a) closing and b) nullifying (Set = Nothing). And I always incorporate it into the layout of the error handling such that, regardless of whether or not an error occurs, those two steps are carried out.

Á la :

Code:
Private Sub X()
 
  On Error Goto ErrorHandler
 
  Dim dbs As Database
  Dim rst As Recordset
  ....
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT...")
  ....
 
Exit_X:
 
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Sub
 
ErrorHandler:
 
  Call LogError(Err.Number, Err.Description, "X", "modModuleName", varParameter, True)
  Resume Exit_X
 
End SUb

I even do it with objects that are unlikely to cause a problem once the sub or function is completed (e.g. the FileSystemObject and derivative objects such as folders etc.), just because I feel it is good practice generally, and by following a convention, I am less likely to forget to do it somewhere down the line where I really need it.

My question, therefore, is : if your opinion is that the cause of my "Too many users" error may lie with the supposition that :

For some reason, your connections that you establish from the FE files are not getting closed to the BE file, so the connections accumulate

...yet my code follows a strict convention to ensure any and all such connections, created within the code, are subsequently closed within each private sub or function, then where should I look next?

Appreciate that it's obviously difficult - impossible, really - to properly isolate the problem without seeing the actual code (of which there is a not-inconsiderable amount) so not looking for a perfect answer - and not expecting one.

Suffice to say, not having touched the FE since this problem first arose, there haven't been any further instances of it happening, nor did it happen at all in the 6-7 months since it first went into production. With any user, including the girl whose FE raised it in the first place. Which has me thinking (foolishly or otherwise) that it may have been an isolated incident, which will be rare, and which hasn't had a detrimental impact on the overall process (she was able to get around it by simply closing and reopening the FE)

So do I need to expend any more effort (and your time :o) investigating it? In your valued opinion?

P.S. I would just like to note how much I appreciate the level of discussion yourself & vbaInet have provided on this topic. Regardless of the outcome, I am gleaning invaluable information from you experts which is aiding my general development as an Access programmer, for which I am, as always, extremely grateful.
 
So do I need to expend any more effort

If your problem appears to have gone away, perhaps you can "let this sleeping dog lie" for a while and see if wakes up again. The first rule of fixing anything, whether you are a programmer, a systems administrator, or a plumber - if you can't see the problem, you can't fix the problem. (If you find plumbers who don't follow that rule, don't hire them again.)

One last thought occurs to me: The BE is on a shared network drive and the problem is rare. From your first post, there is the question of whether this could have been a glitch network. I cannot answer that without seeing network logs. Put this in the back of your mind, though, and pull it out if the problem starts happening again.

Do your absolute best to find the time at which the problem occurs. Then go buy your network guru a soft drink or a beer (as appropriate) and ask if anything occurred within a couple of minutes either way of that exact time. If your desktop systems are all running NTP services, I'm talking ONLY a couple of minutes; if you are not running NTP, maybe a little more leeway is needed. Usually you can find the SYSTEM logs but also the DEVICE logs and some systems are configured to have separate NETWORK logs. There are also APPLICATION logs you can review. If you have a precise time of day, it takes almost no time to find relevant events.

Hint: Start with the system logs, not the other logs, because they typically "cast the biggest net" in catching stuff. Since you are using Access FE/BE, you will not need to look at web-server logs, which will make it easier to find what happened. (Trust me, if you had to go through IIS logs, you would hate yourself afterwards almost as much as your network guru would hate you.)
 
@Doc:
One last thought occurs to me: The BE is on a shared network drive and the problem is rare. From your first post, there is the question of whether this could have been a glitch network. I cannot answer that without seeing network logs.

Isn't it what I said in post #2?
Anyway, if it's a transmission error, I doubt you will find anything in logs since any major event would have affected the network more deeply...
 
JL, on second reading of your post, I can see that you probably meant to reference a network error. Your comment, however, was a bit vague because it took a second reading for me to realize what you actually said. Wouldn't be the first time I didn't immediately interpret someone's answer correctly on first reading. Then again, some of my answers get a bit oblique now and then.
 
@CJ: the problem is that to me, English is a second language. So it may explain why my posts are not perfectly clear.

But what about my point on transmission error? It happened to me at Domfer (my last job - now I'm retired) and the only solution I could find was to install optical fiber...
 
Buried or heavily sheathed optical fiber is not more reliable than buried or sheathed twisted pair as long as nobody comes after either one with a big set of garden shears. The only real difference is that you can transmit parity errors far faster over fiber than over twisted pair. Stated another way, twisted pair is usually slower than fiber because you need to broaden the digital pulse widths to improve signal coherence. If the optical fiber made a difference, it was that installing it introduced newer connections that had no corrosion buildup and less exposure to potential corrosion.

It has been my experience that the carrier (fiber/wire) is usually NOT the cause of network failures - it is the routers, firewalls, and switches, i.e. the ACTIVE devices that go bump in the night. The carrier / physical medium is generally passive. Installing fiber just changes the connectors and the network appliances to newer ones at either end of the physical link layer components.
 
@Very Doc:

Your knowledge is like jam: you spread it at large but too much is too much. You know nothing about the plant where I was working but you have an opinion: oh wowwww!

By the way, it was using microwave, a very stupid choice for that kind of industry: foundry and high temp. furnaces. And the choice was made by the director of the plant, an (ignorant?) engineer...
 

Users who are viewing this thread

Back
Top Bottom