Maximum Number of Concurrent Users

[zyousafi]

How many people can use Microsoft Access in a multiuser environment at any given time (i.e. the maximum number of concurrent users)?

 

[boblarson]

Well, first of all, the database should be split Frontend/Backend with the backend (tables only) on the server and the frontend (everything else) should be a COPY on EACH User's machine.

Then you can have a total number of concurrently connected users of 255, however anything above 10 to 25 at the same time will start to slow things down a bit.

If you really need a lot of concurrent users then I would use Access for the frontend and SQL Server or SQL Server Express for the backend.

 

[zyousafi]

I have already split the database into Frontend and Backend. I was definately looking to have more than 10 to 25 concurrent users. Where did the 255 figure come from? Is that something Microsoft says Access can do?

 

[boblarson]

I have already split the database into Frontend and Backend. I was definately looking to have more than 10 to 25 concurrent users. Where did the 255 figure come from? Is that something Microsoft says Access can do?

255 is the official number, yes.

You might want to see if you can use SQL Server Express if you don't have full SQL Server available to you. It also stores 4Gb of data instead of 2Gb that Access can store.

 

[MoxieCraz]

I have a front end and back end split. I have a max of 7 users. I have noticed it seems to slow down if 3 users are in at the same time. Is there anything I should check to increase speed performance with multiple users?

 

[boblarson]

I have a front end and back end split. I have a max of 7 users. I have noticed it seems to slow down if 3 users are in at the same time. Is there anything I should check to increase speed performance with multiple users?

Perhaps this page will help.

 

[MoxieCraz]

Very interesting. Has anyone tried this?

http://www.granite.ab.ca/access/performanceforms.htm

 

[MoxieCraz]

It's Friday...need I say more. This should be easier than I am making it. Here is whats in my Record Source:

SELECT Elements.*, Contacts.[Last Name], Contacts.[First Name], Contacts.Company, Contacts.[Job Title], Contacts.[Business Phone], Contacts.City, Contacts.[Last Name] AS [Last Name_Contacts2], Contacts.[First Name] AS [First Name_Contacts2], Contacts.[E-mail Address] AS [E-mail Address_Contacts2], Contacts.[Job Title] AS [Job Title_Contacts2], Contacts.[Business Phone] AS [Business Phone_Contacts2], Contacts.City AS City_Contacts2, Contacts.[State/Province] AS [State/Province_Contacts2], [Business Units].[Business Unit Description], Contacts.Attachments AS Attachments_Contacts2, Contacts.Event, Contacts.[Event Name], Contacts.Attachments AS Attachments_Contacts FROM [Business Units] RIGHT JOIN (Contacts RIGHT JOIN Elements ON Contacts.ID=Elements.ContactID) ON [Business Units].ID=Elements.BusinessUnit;

According to the above website I should be able to use the following in the form's load event:

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

Any takers on how to convert what I currently have to the suggestion? I think once I have an example of my own data, then the other dozen plus should make more sense. Thanks for your help.

****Or if anyone has a database example to post. Thanks.

 

[boblarson]

It's Friday...need I say more. This should be easier than I am making it. Here is whats in my Record Source:

SELECT Elements.*, Contacts.[Last Name], Contacts.[First Name], Contacts.Company, Contacts.[Job Title], Contacts.[Business Phone], Contacts.City, Contacts.[Last Name] AS [Last Name_Contacts2], Contacts.[First Name] AS [First Name_Contacts2], Contacts.[E-mail Address] AS [E-mail Address_Contacts2], Contacts.[Job Title] AS [Job Title_Contacts2], Contacts.[Business Phone] AS [Business Phone_Contacts2], Contacts.City AS City_Contacts2, Contacts.[State/Province] AS [State/Province_Contacts2], [Business Units].[Business Unit Description], Contacts.Attachments AS Attachments_Contacts2, Contacts.Event, Contacts.[Event Name], Contacts.Attachments AS Attachments_Contacts FROM [Business Units] RIGHT JOIN (Contacts RIGHT JOIN Elements ON Contacts.ID=Elements.ContactID) ON [Business Units].ID=Elements.BusinessUnit;

According to the above website I should be able to use the following in the form's load event:

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

Any takers on how to convert what I currently have to the suggestion? I think once I have an example of my own data, then the other dozen plus should make more sense. Thanks for your help.

****Or if anyone has a database example to post. Thanks.

Not exactly sure why he has
Me.txtSomeField.RowSource = "xxx"

Because a text box which is what txt usually signifies, does not have a Row Source. Only a combo box or List box have those.

 

[MoxieCraz]

yes, I was confused on that too. thanks. I will try it without it. thanks.

 

[MoxieCraz]

Still not sure I am getting it.

Is the "Select Somefield " & _
suppose to be everything after the SELECT Elements.*,
and would the "FROM qryLargeTable " & _
be every table that is used in the query?
and there would be no group of order?

This seems like a lot of rebuilding of how Access creates the record source...am I making this more complex than it should be?

 

[MoxieCraz]

Anyone that can interpret the SQL from what I currently have in the Record Source...I could use some guidance. Thanks.

 

[Djblois]

Sorry for bringing up an old topic but I am in the process of creating a Multi-User database for Reporting purposes. The Front End will be Access but the Back end is going to 98% of the time be Oracle. There are a very small number of times that I may use an access table and/or Excel worksheet. What are my limits? and would the fact that for the most part be Oracle speed it up at all?

 

[gemma-the-husky]

or try this one

http://support.microsoft.com/kb/889588