I have a max of 200 employees here at work, and they all log into thin clients and running on two terminal servers. I am starting to write a function in VBA that is going to have the following Public variables:

23 long integers,
58 strings,
18 dates,
1 currency &
1 boolean

Now, my question is: Hypothetically, if all 200 employees ran this function around the same time while using the same terminal server, what kind of memory usage could I expect at that instant? let's assume that each long integer is holding eight digits and each string is holding 255 characters

If all of that memory (multiplied by 200) is less than 32MB then I have nothing to worry about. Could the variables consume more memory than that?

except for strings which can be very long, the other variables are 4 or 8 bytes. One wonders what you are doing to NEED explicitly 58 strings

but I doubt very much if memory allocation of those variables would be an issue per se - in any event there is far more being held in volatile memory than variables for your 200 processes - the issue of multiple concurrent users is more of a general thing, of which the variable storage is the tip of the iceberg,

An os manages multiple users (or indeed a single user having multiple processes - look at the task manager in a single-user system to see how many processes are REALLY running concurrently - concurrently being the operative word), by allocating a portion of the available memory to each user/process for programme code /data being processed and variable storage. To give the impression of concurrent usage, users are serviced via interrupts and queues, with each user or process typically receiving only a few milliseconds of processing time when they reach the head of the appropriate queue.

As more and more users work concurrently, the memory gets fuller, and at some point the memory is completely full. consequently the next time a process instructions tries to execute a command, it might not be able to because

a) the bit of the programme containing the required instruction is not currently in memory or
b) the bit of data to be processed is not currently in memory

Now as the memory is full, it means that some of the existing memory contents has to be swapped out (to hard disk) to enable the new data to be fetched - hence the swap file on the disk. The os will have a strategy relating to page size, page cluster size, timing of paging out, process processing queues - even hard disk reading strategy - as it tries to retain in volatile memory the most likely pages to be accessed (this is one reason why defragging the hard disk also improves performance - a hard disk is also logically arranged in pages - and the point is that programme instructions, and data being processed are often near one another on the hard disk - so when a cluster of pages is read into memory from the disk there is a likelihood that data or programme instructions that will be needed next, ARE on one of the contiguous pages - if the data is badly fragmented, then multiple disk reads become necessary to retreive all the programme code and data)

now from the above it follows that there is what's called a minimum working set, which is effectively the miminum amount of data and programme code a process/programme needs in RAM to work efficiently, (so that a sensible amount of programme code/data and variables are present, and the amount of swapping is managed at a sensible level)

once you get too many users/processes, the page space available for each user becomes too small to maintain these minimum working sets for each user, Now the cpu still works, but instead of spending most time computing, and a little time swapping pages, it spends more and more time having to swap pages, and consequently has less time to actually run the programmes, and perceived performance begins to dip - this process is called thrashing

some os's are better than others at managing multi users (eg VMS is extremely efficient -windows less so). When you first load a whole programme, you only need a small portion of the programme to be available initially - for instance with Access, you dont need the code for Database Utilities, or the Relationship Window, or the Database Documenter loading at the beginning - The os itself will determine how much of the programme it HAS to bring in (based on its page size etc), but the program design will have an effect on how it is able to bring in just a portion of the programme. With a database, I suspect it would be better to have numberous smaller code modules than one large code module, for this reason.

In any case its hard to be presriptive - I think it depends very much on the amount of loading each user is requesting from the server -some tasks are much more computationally intense than others -200 users accessing 32Mb of memory sounds as if they would be stretched pretty thin, but if they arent all concurrent, and they're doing low computational stuff like word processing, it might not be too bad - but 32Mb between 200 users is only 160K each, and that doesnt take into account OS process requirements.

So overall its not the problem of calculating the memory requirements for 200 users worth of variable storage - its the effect of balancing and managing the load on the computer caused by having 200 concurrent users

Its really a case of suck it and see - although i'm sure there must be some tools/techniques to help analyse system loading to determine the most efficient server structure.

hope this helps - if the above analysis isnt quite right, some learned folk here will chip in and add improvements.

Thanks for the in-depth Gemma. I might be building an application that users can enter data into. It may consist of about 4 or 5 forms or steps. We get pages of reports (yes, hardcopies). It is not possible to get these in softcopy, so we are going to manually enter in the data, have the form save the data in public variables, and then let the calculations fly. I just needed to make sure that this application doesn't consume a lot of memory with 200 people running it at the same time. It's not that I care (that's exactly what the page file is for...) but the IT dept will flip if they see a bunch more memory being consumed all of a sudden.

I didn't really know how much memory was used for the variables but if it's only 4 or 8 bytes each (not including strings) then I guess the IT dept can go jump off a cliff if they really care that much.

Thanks again!

as i say - the memory usage to handle a few variables isnt significant, but the load on the server of 200 concurrent users will most likely cause some real problems

Gemma - excellent summary regarding memory usage in a shared environment. Yep, when you reach the point that physical memory won't hold any more, you are going to stop working soon. And there is an old saying from my early VMS days that seems relevant to this question: "When the system swaps, the world stops." This occurs because the priority of the swap manager is higher than the priority of any general user. You are virtually guaranteed to lose the priority race.

Ross, the CORRECT way to do this is to run TWO users on your server using the Thin Client approach and, from the server, call up the system performance monitor to examine the memory consumed by one user for Access. THAT is the number that you really need to consider. I don't know that Access is designed for sharing in a multi-user environment. It usually takes extra effort.

Gemma mentioned the VMS operating system. This discussion applies to UNIX and VMS (now OpenVMS). When you want to share code, you have a couple of choices. The best way is to compile and link your code so that it subdivides into two segments - a "pure" segment that contains only code and an "impure" segment that contains only data. Then you keep one copy of the pure segment in memory and use the hardware memory management tricks so that everyone points to the same pure segment. Then you allocate only the "impure" segment for each user. You therefore have one copy of the Access code and 200 copies of data. Never tried this for Windows, but I know it can be done for OpenVMS and newer UNIX boxes.

Here's the catch. At least in theory, Access itself should be "pure" - but it has to have been compiled and installed in a way that allows it to be treated that way. It does no good to compile it "pure" if you don't install it "pure" and if the O/S doesn't allow it. If it is not pure, then what happens is that Access loads to memory and pointers get loaded to the high part of Access's memory pseudo-partition to point to the database file. Which instantly makes that an impure segment if it was not loaded right.

My lack of certainty is because I have always worked with the one-user-per-copy version of Access. For that copy, it is almost a certainty that pure code and sharing are not enabled. Thin-client Access is a beast I have never actually touched and I don't know what, if anything, must be done to enable "purity" of the program segment.

Other forumites, can you offer any narrow insights on that question? Is the off-the-shelf copy of Access enabled for sharing via pure/impure segments? Or do you need to get a special copy thereof?