Question on user capacity - i.e. how many can be using single file...

[odin1701]

We have about 170 employees here.

Currently we have probably 10 or so databases - they are Access based.

I am working on a project to combine the 10 into one - there will be some duplication which is removed due to having them all centralized (date entered/completed fields, user initials fields, etc.).

Now - my question is, is 170 people too many to be using a single MDB file from a front end? Especially since they will be working with the same tables?

I've not had a DB where that many people used it that wasn't in a SQL server, so I'm not sure how Access will handle this. Any suggestions welcome.

Thanks for the help.

 

[Banana]

I'm afraid there's no easy answer because there's so many variables.

Some scenarios, Access dies out at just 25 concurrents users. In other scenarios, it's just fine for 200 users. (I seem to recall a real cap at 255 concurrent users, but the actual number may be less because this is counted by connections, rather than users, and some users can have more one connection)

A large portion of this will depend on how well designed your database is. If you have few forms that has tables as recordsource, it will definitely hammer the whole database and you'll be hearing complaints from users. But if you used queries, especially with WHERE clause to selectively filter the amount of records, it will help a bit.

Then there's form design. If you happened to have a form that had 20 comboboxes, 4 listboxes, and 3 subforms with grand total of 6 comboboxes, you are actually sending in *30* queries just to load that form, and that again, can bring the database to its knee and crying like a baby.

A common solution is to use tabbed forms, where on initial load, the other hidden tabs' subform recordsource is empty. Until an user click on the tab, only when the recordsource is loaded; basically you load it just in time when the users actually needs it.

I hope that helps give you some ideas.

 

[odin1701]

I pretty much always use queries for form recordsources.

Most forms are pretty basic. There are only a few which are more complex.

Users will only have access to the forms which are for their processes, so they can't just open anything.

 

[Banana]

That's good.

It may just mean all you would need is to tweak the queries to limit the amount of rows until users ask for more rows to provide a slightly better performance, if you ever find it necessary.

The only way to know when you've maximized Access's capability is to actually come to that point. After that, you'll have to start thinking about migrating to more robust RDBMS solution.

 

[Guus2005]

Using bound forms is with that many concurrent users risky to say the least. IMHO it is better to work stateless using unbound forms.
Since your in the process of combining 10 databases into one, why not use unbound forms to reduce the amount of traffic and connections and increase response time.
Use one BE and a FE for each user and create a way to log user out when they're logged in and not using the database.

share and enjoy!

 

[odin1701]

Using bound forms is with that many concurrent users risky to say the least. IMHO it is better to work stateless using unbound forms.
Since your in the process of combining 10 databases into one, why not use unbound forms to reduce the amount of traffic and connections and increase response time.
Use one BE and a FE for each user and create a way to log user out when they're logged in and not using the database.

share and enjoy!

I'm not sure what you mean by using unbound forms. How would I do something like this?

 

[Banana]

Basically, all your forms would have no recordsource. You'd then manually code everything to navigate through records, editing a record, adding a record, and so forth.

 

[odin1701]

Basically, all your forms would have no recordsource. You'd then manually code everything to navigate through records, editing a record, adding a record, and so forth.

I'm not sure how to go about that.

You would have to have a recordsource setup at some point, whether it be in code or the form....

For one process we have about 650 records which are open (many more that are in archived state). They need to have access to all of them and be able to filter by account number, etc. So I would have to have some sort of recordsource filled with those records, right? How is it different doing it in code vs. the form?

I'm not sure how I would code it with no form recordsource.

If I did end up doing that, then I may just move the front end over to a visual basic program vs. Access.

 

[Banana]

That's exactly my feeling- Access is there to provide you quick way of building interface to data source, using bound forms. If you start to go unbound for all of forms, you might just as well use a real programming language and create a real .EXE because the amount of work for unbound form is not that much from other language (not to mention that other language may excel at giving you more control than if you did it within VBA).

There's few ways around this.

1) By far the best way is to be anal-retentive with your queries for forms' recordsource, combobox/listboxes' rowsource, subforms' recordsources. Use WHERE condition to limit the amount of rows returned. I don't know what your scenario is but an example would be to only load last two weeks worth of records and if users need to go past that, click Archives or something to look further back. Also, limit queries sent by either forcing users to work with only one form that's loaded, or if you have a tab control with subforms in each tab, do not load the recordsource until the user click on the appropriate tab (done by setting form's recordsource to empty, then on OnChange event of the tab control, setting the form's recordsource for which tab is selected).

2) You could have one form for viewing only with a separate form for data entry. Again, this depends on whether this is appropriate for your situation; with a data entry form, all you would be doing is sending INSERTS without any SELECTS, improving on the performance. The basic idea is that if your users have a speciaized functions (e.g. a group of data entry operators, a group of sales manager for example), then provide them with a specialized forms.

3) You could just migrate the backend to a RDBMS (MS SQL, MySQL, PostreSQL, Oracle, etc.) and applying #1 to provide good performance with bound forms for Access front end.

HTH.


PS: A question: How likely is it that two users would access same record at same time? (e.g., is an account usually dedicated to one user, or can be shared among several user or whoever answers the phone first and so forth?)

 

[odin1701]

Accounts are usually assigned to associates who work them, and then if they finish their part, they assign to another associate. They usually bring up a form filtered for accounts assigned for them. Though they do sometimes need to look through all accounts to answer customer questions if an associate is out or something. Also, sometimes a single record may be accessed at the same time - if not because of a supervisor looking at something, then the Quality Assurance team may be going through things. Also reports are run which would access all records, etc.

Data entry also varies between processes. Some have an import process which pulls account information from our mainframe system and enters it into the database as a new record. Other processes manually enter their information as there is less of it and most isn't in the mainframe - mostly just information to track an account through their process.

I can't just pull the last two weeks. For example, the process which has about 650 records right now - about 50% are over two weeks old, but are still being worked (a lot of wait time involved in this particular process). Accounts are archived - simple yes/no datatype - the query ignores them for the main form as there are over 30,000 records in archive. That archive data is still used in many reports. Occasionally an account has to be un-archived and there is a seperate form/method for this.

There's just a lot going on. Even if I did this in an actual VB program, I would still need to have a front end database just for doing all the reporting for the various processes.

 

[Banana]

The reason I asked was because if it was very likely that a bunch of users could be working on same record at same time (and not just viewing the same record at same time), you would have problems with record locking, which I understand, isn't one of Access's forte. But this doesn't sounds like that is the case, so you may be OK. Just keep that in mind.

I threw out the "two weeks" as an example. As I said, you'll have to decide what is most appropriate for your scenario and restrict the query to that. Another idea is to instead of filtering for your associates, send only what they're working with, and if they need to look at another associate, they'll have to explicitly do so (by selecting a combobox for example). This may or may not be appropriate for your sitation, but the point is that if your users start to complain about poor performance, even thought everything else is working correctly but not fast enough, this is what you would usually do to give better performance.

I hope that makes sense.

 

[odin1701]

Yeah I could create a combobox with distinct values of initials that accounts are assigned to, plus a "*" option so if needed they could open all records - otherwise they would be working with just theirs.

I think that might work out better - just use that combobox to set the recordsource.

 

[Banana]

Good luck!