What causes a front end to work for me, but run out of stack space for another user

[Adam Tippelt]

I've split a database into a back end containing the tables, and the front end containing everything else.

I've uploaded this to a shared drive, and applied the following security settings:
- Disabled Document Tabs
- Disabled Access Special Keys
- Disabled Navigation Pane
- Disabled Full Menus
- Disabled Default Shortcut Menus

When I try running the front end, either from a copy on my own desktop, or the copy on the shared drive, it works absolutely fine for me - it brings up my login form, I can login and access everything else.
However, I have a collegue in Singapore who is now trying to access the front end. Everytime he loads up the front end (either directly from the copy on the shared drive, or by copying it to his desktop) and tries to login, the whole thing stops responding, and after a few minutes it eventually displays a message box saying "Error detected: Out of stack space."

Once this error has happened for my collegue, when I then try the same front end, it happens for me too - it's as though something happens when he tries it, which corrupts the entire file.
What I've noticed is when it starts to not respond, in the bottom right corner it says "Run query" next to a progress bar that is stuck on 0%. If I disable the query it's getting stuck at, it will proceed, but the problem occurs at the next query that tries to run.
My collegue has opened this same file a few months ago without any problem - however this was before the database was split, and before I disabled any of the options listed above.

Can anyone offer any possible suggestion why this is happening, and how I could fix it? I am completely stumped as to why this is happening and don't even know how to go about debugging this problem!

Thanks in advance.

Adam.

UPDATE: Because the error message is causing my collegue to close Access via Task Manager, it leaves his connection open. Once this connection is killed, the front end works absolutely fine for me again - so the problem seems to be specifically with him.

[NeoPa]

Adam, I suggest you need to find out some more information before exploring this issue.

Why don't you create a debug version (See Debugging in VBA) of the FE for your colleague with various Debug.Print lines in the startup code saying what has executed and timestamp each message. When your colleague gets the error message he should go in and copy the messages from the Immediate Pane of the VBA IDE and send them to you. Fiddly I know, but it can be quite flexible and at least you'd have something to ask about in the question.

Also, where is the BE database in relation to each of you network-wise? If your colleague in Singapore is accessing it over a WAN then you can always expect serious problems. Access has always been particularly sensitive to network connections. If any Access packets within a session fail to be received then the session dies without recovery and db corruptions often ensue. WANs are very rarely if ever able to guarantee continuous connection in the same way LANs can.

[jimatqsi]

Can you verify the user has access to the data? Can you make a copy that lets them get to any linked table to demonstrate they actually have rights and the ODBC is working?

Jim

[Adam Tippelt]

@NeoPa: Debugging like that is probably not going to work unfortunately - I don't think my colleague understands enough about the back end side of Access to do something like that.

The back end in relation to me is on a server that I believe is in the same office as I am, based in the UK. Obviously this is quite a distance from Singapore, and I get the feeling that you might of found the potential problem. If the network is the problem, are there any ways of getting round it, with the back end remaining in a single location?

One thing to add is that my colleague has tried accessing it both via a remote connection and from his standard connection - I don't know if this has any bearing on bypassing the network type (I'm guessing not) but both times he has the same problem.

@Jim: Do you mean do something like enable the navigation pane, and check if he can open a table via that?

[Adam Tippelt]

Just got this from my Sys Admin about the server that the back end is located on:

The P Drive sits on an ESX based server, this server has a very large capacity and is split into separate drives.

P:
X:
T:
H: etc…

The Singapore office has a dedicated “VPN” link into the network, when a user in Singapore logins in the network login script is run that then maps the above drives for them.

It works in the same way it does in the UK office.


Dunno if that helps at all or not.

[NeoPa]

That helps Adam. That is not a remotely tenable situation for Access (sorry).

Fortunately though, there is a solution if you can set up a Remote Desktop Connection from a machine in Singapore to one that is local to the server. Access runs on the local one, but contolled by whoever is runnin he remote one.

[jimatqsi]

Yes, that's how a client of mine connects his outside sales force. They establish a vpn, then connect via Remote Desktop and login in a manner similar to what Adam describes. The problem is the strict limit on the number of available Remote Desktop connections at one time.

Yes, Adam, I meant giving the user a modified version of the program. But from what NeoPa says, you just can't in that environment. I wonder if that is still true with more recent versions of Access.

jim

[Adam Tippelt]

My colleague has tried it over a direct connection, a VPN connection, over Terminal Services, and all resulted in the same problem.

This may be a daft suggestion, but would it be possible to have an unsplit database, but also with a split front end? Let me explain:

The colleague in Singapore ran the application without trouble before it was split, so to get it to work for him again it would make sense to unsplit the database. However, doing this loses the disadvantage of the split - better performance and data integrity in a multi user environment.
So what if I had a split database, but both the front-end AND the back-end contained a copy of the forms, reports, queries, code? This way I could distribute a separate front end to UK users, but for the Singapore location they could work directly on the back end, thus eliminating the connection problem. All security features I listed in the original post could be applied to both ends (the Shift Key bypass has been left in, as most users don't know it exists, and can be used for table access if ever needed).

Can you see any problem with this situation? I know multi users on an unsplit database can cause problems with data being changed, however the current design of the database has a user working on a temporary snapshot of the record they want to change, in a different table, as opposed to editing the live data.

I know this isn't an ideal solution, but it's a limitation of the software that I don't think our network can fix.

[NeoPa]

I cannot see any obvious problems with that Adam (well a few ideas make me somewhat nervous I accept, but it seems you have a design that should bypass the problems if I understand your proposal clearly enough). I would more than hesitate to guarantee it, but I would certainly try that myself if I couldn't get to the root off the real problem and couldn't find another solution. Personally, I'd put more effort into finding out the real problem, but I appreciate that you're not in the same position I would be in, as you couldn't be expected to have the debugging experience I've had to pick up over the years.

All in all, go for it and see what happens :-)

[Adam Tippelt]

I'd put more effort into finding out the real problem

Can you expand on what you mean by this? I thought it was a problem with Access itself as opposed to something I had done?

Thanks for the response though, I'll see how this idea goes...

[Adam Tippelt]

One other thought - if I was to migrate the back-end to SQL Server is that likely to solve the problem?

[NeoPa]

NeoPa:
I'd put more effort into finding out the real problem

I merely meant if I were in your shoes with my experience, that's what I would do. I cannot say it's what you should do as I don't know enough about how well you could do that, but it's worth consideration, even if your considered approach is not to do that in the end.

[NeoPa]

Adam Tippelt:
One other thought - if I were to migrate the back-end to SQL Server is that likely to solve the problem?

I expect it would yes.

Access doesn't really work as a BE at all. It really works as a FE pretending to be a BE. There is no service running on the remote machine. On the contrary, the FE session opens a connection itself to the BE database. Quite a different animal. I expect these problems (assuming they are related to the network issues as discussed) would disappear if you were to use a SQL Server BE instead (Of course, a whole new set of difficulties may replace them, but that's another story :-D).

[Adam Tippelt]

I merely meant if I were in your shoes with my experience, that's what I would do. I cannot say it's what you should do as I don't know enough about how well you could do that, but it's worth consideration, even if your considered approach is not to do that in the end.

Yes I understand that, I was wondering if you'd give any suggestions as to what else COULD be tried, as I thought I'd exhausted all options (Clearly that's the difference between years of experience and 10 months of experience :) ).

Either way, I will have to investigate the SQL Server route - I know they use SQL Server 2008 here, so it would make sense to migrate to it, but I know nothing about SQL Server (yet...) and like you said there may be other problems.

Thanks for your help, it has once again led me back into researching a new topic. :)

[Adam Tippelt]

I guess I'll have to change from DAO to ADO for any recordsets I have?

(Sorry, kind of going slightly off topic now, perhaps I'll make a separate thread...)

[NeoPa]

I would only add that SQL Server 2008, while it is certainly a very robust and powerful BE server system, is not a trivial topic. Actually, referring to it as a topic indicates a level of underestimation of the issue. I would certainly recommend caution approaching this alone, without some years of database experience behind you.

I appreciate this doesn't reassure you, and I'm sorry the straightforward solution is not so easily reachable, but I felt I ought to give you the heads-up. On the positive side, we do have a SQL Server forum here with a number of very experienced experts, but I have to warn you also that the level of activity there, as in most other forums of the site, does not even closely match that of this (Access) forum. There simply isn't another forum as busy. Only .NET even comes close.

That's the situation anyway. I won't warn you off using MS-SQL, but you should be aware that it's no stroll in the park for the inexperienced.

[Adam Tippelt]

In the time it took for your response I've been reading articles about migration, and just from reading one article I already understand what a massive job it would be - reading through, I think migrating (properly) to SQL would require a complete re-design, as a lot of stuff would need to become stored procedures, subforms redesigned to handle the change in autonumber handling, recordsets re-written for ADO - basically a lot.
Given that I was only supposed to be designing a prototype for what will eventually become a web-based application, it sounds like migrating to SQL Server would be taking the prototype further than it's supposed to go. If I was going to the hassle of redesigning the back end to handle SQL Server, I would probably want to redesign the front end anyway, and rebuild it in a web-based language instead of Access, which kind of negates the whole prototype stage and greatly increases the development cycle of this application.

Yes I think I originally underestimated what the migration would require, but now I know more I think it's fair to say that a migration is beyond this application at this stage.

Thanks for the heads up about SQL Server, and for confirming my suspicions.

[Rabbit]

How much RAM does each computer have and how large is the virtual memory? Is there a large query/function that runs? Is he getting stuck in an infinite loop that leaks memory due to an unforeseen circumstance? Do you have a recursive function? Those are just some possible issues.

Here's more:

The stack is a working area of memory that grows and shrinks dynamically with the demands of your executing program. This error has the following causes and solutions:

You have too many active Function, Sub, or Property procedure calls.
Check that procedures aren't nested too deeply. This is especially true with recursive procedures, that is, procedures that call themselves. Make sure recursive procedures terminate properly. Use the Calls dialog box to view which procedures are active (on the stack).

Your local variables require more local variable space than is available.
Try declaring some variables at the module level instead. You can also declare all variables in the procedure static by preceding the Property, Sub, or Function keyword with Static. Or you can use the Static statement to declare individual Static variables within procedures.

You have too many fixed-length strings.
Fixed-length strings in a procedure are more quickly accessed, but use more stack space than variable-length strings, because the string data itself is placed on the stack. Try redefining some of your fixed-length strings as variable-length strings. When you declare variable-length strings in a procedure, only the string descriptor (not the data itself) is placed on the stack. You can also define the string at module level where it requires no stack space. Variables declared at module level are Public by default, so the string is visible to all procedures in the module.

You have too many nested DoEvents function calls.
Use the Calls dialog box to view which procedures are active on the stack.

Your code triggered an event cascade.
An event cascade is caused by triggering an event that calls an event procedure that's already on the stack. An event cascade is similar to an unterminated recursive procedure call, but it's less obvious, since the call is made by Visual Basic rather than by an explicit call in your code. Use the Calls dialog box to view which procedures are active (on the stack).

To display the Calls dialog box, select the Calls button to the right of the Procedure box in the Debug window or choose the Calls command. For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[Adam Tippelt]

Wow. Thanks Rabbit, that's incredibly detailed.

I'm either being completely blind or looking at the wrong thing, but I don't understand what you mean by the Calls dialog box - there's nothing in the VBA Editor screen that even sounds remotely like Calls Dialog Box. Am I supposed to be looking for this in the VBA editor during the problem, or can it be accessed at anytime?
Also if by Procedure box you mean where you actually write the procedures, there is nothing to the right of it - it extends all the way to the edge of the screen.


Regarding your suggestions of what to check, as I stated before the first query found causes a stack problem, even when commented out it's whatever the next query is, so it doesn't seem to be a specific query.

The first query found is a simple append query to add the current user's username to a table that tracks what date/time a user logs in (date column defaults to =Now() ). The WHERE clause of the query compares the username entered in the login box against the Users Table, which currently only contains 5 users.
Aside from this, the query is completely standalone - there's nothing nested, nothing triggered, and in it's current state, its only having the send 5 user records over the connection for the comparison.
This is run on the login screen, before the user has had access to anything else. Without seeing this calls dialog box you refer to, I can't be for sure, but the only other things that could possibly start running are the modules, but none of them have been used, so why would they?
So going on that information alone, the only thing being run is an incredibly small query, and yet it gets a stack problem, would that not suggest that this isn't a coding problem?

Thanks.

Adam.

[Rabbit]

The calls dialog is available in the VBA editor. If you put it into debug mode, like with a break point or something, you can click View > Call stack and it will show you what procedures are active on the stack.

Do you get the error if you disable everything? What happens if you disable the macros and code?

Also, how much RAM does the other computer have? Is he pushing up against his virtual memory limit?

[NeoPa]

Adam explained earlier that the problem only occurs in Singapore, where the only operator is not someone he can trust with any debugging features. I don't believe debugging on his working version would give him any useful information about the problem.

I mention this as I know that it makes sense to skim long-standing threads and I felt you'd maybe missed these points that I've synopsised (Is that even a word? My spell-checker thinks it's ok.) for you.

PS. @Adam, there is also a button on the debugging toolbar that gives you access to the Call stack. It looks like overlapping squares. Ctrl-L is a shortcut.

[Rabbit]

What I've done in cases where the user is at a different geographical location is to remote into their computer with dameware, vnc, or remote desktop and troubleshoot from there.

[NeoPa]

I'll let Adam respond to that Rabbit. I agree, I would too. We are both possibly more IT orientated than Adam though. When I asked earlier he seemed to feel the debug approach was not profitable in his circumstances. Maybe your post opens up new avenues he hadn't considered before. Only he can say really.

[Adam Tippelt]

Do you get the error if you disable everything? What happens if you disable the macros and code?

As I said before Rabbit, the problem occurs when attempting to run a query - this is not caused by a specific query because I've commented the query out, and when you run the file again it crashes on whatever query is found next.

The calls dialog is available in the VBA editor. If you put it into debug mode, like with a break point or something, you can click View > Call stack and it will show you what procedures are active on the stack.

Checking the stack is not possible in this situation either, as the message box displaying the out of stack message is not a run time error box, it's a standard message box with only an ok button, and clicking ok just makes the box appear again - you cannot enter debug mode because the only way to leave the infinite loop of message boxes is to close the application via task manager (ctrl+break does not stop it)
When checking the stack on a working copy at my location, there is only one item on the stack at the time where it fails in Singapore, which is the event that triggers the query.

What I've done in cases where the user is at a different geographical location is to remote into their computer with dameware, vnc, or remote desktop and troubleshoot from there.

While I cannot create the problem myself, once it's occurred I can see the problem, as the Singapore connection is still open (due to it being closed by task manager), and therefore I can troubleshoot from here.



The same person tested the same application a few months ago, before it was split, and had no problems, so surely it must be the network?

(While it looks like I'm fighting your ideas, I do very much appreciate your input and am certainly learning a lot!)

Thanks.

Adam.

[Adam Tippelt]

I'll let Adam respond to that Rabbit. I agree, I would too. We are both possibly more IT orientated than Adam though. When I asked earlier he seemed to feel the debug approach was not profitable in his circumstances. Maybe your post opens up new avenues he hadn't considered before. Only he can say really.

You're almost certainly more experienced in IT than me - I am actually a University student on a placement year, which is why I face certain restrictions in both what I know, and what I'm allowed to do in the given time.

[Rabbit]

If you're commenting out a query, then that means the query is being run by code. What happens if you disable all the code? And then what happens if the user attempts to run the query manually? Is it a simple query? Try a simple select query with a top 100 and see if that works.

Looking at your call stack is different than looking at the call stack from the machine where it failed. Your machine is not going to show what procedures are running on his machine.

If none of this works, the company is going to have to look at upgrading their database system. MySQL is a good choice if money is an issue. Or perhaps a web based front end would suffice.

[Adam Tippelt]

Previous posts already discussed a migration to SQL Server 2008 which the company uses.
I've spent the past day or so playing around with SQL Server 2008 Express before I talk to anyone about putting it on the actual server.

I've done a simple migration which converted 100% without any major errors, but there is a performance issue.

To save going off topic from the original post I'll create a separate thread for any further queries, but I think we've pretty much established that an Access based back-end is not ideal.

Thanks for all the information you both gave, it's very insightful.

Adam.

[NeoPa]

Thanks for the update Adam. We can consider this one closed then :-)

[Adam Tippelt]

Ironically I think I found the cause of the stack problem:

I've been playing around with the back end migration I've done to SQL Server 2008 Express, and managed to stumble upon an Out Of Stack Space error myself (albeit in a different section).

Having caused the problem myself, I took the opportunity to take a look at the stack - my my were you right about the stack looking different during an error. :)

For my error trapping I have 2 procedures - one that displays errors to users, and then it calls the other procedure to log it in a table.

Turned out there was an error in a procedure I use to log errors to a table, and on error this procedure was actually set to reference the other error procedure that triggered it. That'll teach me to pay more attention when adding error trapping code!

Anyway, yes we can consider this closed as the original thread question is now fixed. :)

Thanks.

Adam.

[Rabbit]

Excellent! Glad we could help you figure this one out, even if it took a while to get there.

[NeoPa]

Adam Tippelt:
Turned out there was an error in a procedure I use to log errors to a table, and on error this procedure was actually set to reference the other error procedure that triggered it. That'll teach me to pay more attention when adding error trapping code!

That'll certainly do it Adam :-D

It's one of those issues you are never going to be able to answer remotely. On the basis that if the questionner has any idea where to look they generally get the problem and don't need to ask. Well found though. I think you can rest assured that was certainly the cause of the issue.