Multi-User Split Access Database Freezing

[data808]

MS SQL Server Express is freeware, but database size is more limited (but anyway more than for MS Access), and biggest minus with it is, you can't run jobs like in licensed version (in case you want to schedule procedures to automate tasks you have to use other means for it - e.g. Windows Scheduler, which will run the script sending commands to SQL DB to run procedures there).

I see. Thank you for this info. I will bring it up if we ever come to a crossroads on deciding how to best proceed to meet the needs of our department. Without an SQL server, would you happen to know what is a comfortable amount of simultaneous users to be logged on with minimal risk of freezing the database? Is there a rule of thumb to go by?

Thanks for your help.

[Micron]

The published limit as per M$ is 225 concurrent users (or is it 255?). I suppose that would be under the utmost ideal conditions and maybe more theoretical than practical, so would 25 really be an issue? If it was, it just might be due to other issues that diminish the limits of ones database.

[isladogs]

255 users is the absolute limit but 25-30 users is the practical limit in terms of performance.
As for the free Express version of SQL Server, it has a size limit of 10 GB which compares favourably with the 2 GB limit in Access.

[data808]

Have you checked your backend tables for any signs of corruption? Chinese type characters?
Do you do regular compact and repairs? How big is the backend?

Make sure you do frequent back ups of your data.

Just wanted to report back and keep this post alive. I just did a compact and repair so let's see how that goes. I only applied the compact and repair to one of the backend files which is the main one they use very frequently. The other backend files aren't used as much and are much smaller in file size so I will leave those alone for now.

Still working on getting information on error prompts from the users. Once I have that I will report back. Thank you for all your help everyone.

[data808]

255 users is the absolute limit but 25-30 users is the practical limit in terms of performance.
As for the free Express version of SQL Server, it has a size limit of 10 GB which compares favourably with the 2 GB limit in Access.

Interesting. Thank you for that info. Hopefully it's not hard to setup if I go that free express route.

[data808]

UPDATE: Users reported that they got the error message again but by the time I went down to their department someone already logged off all the computers that had the error message so I was not able to see what kind of error it was. I told them to please screenshot the error and email it to me when it happens again. So I guess the compact and repair that I did last week did not help.

One thing that I can think of that may be causing this freezing where users can't edit records is maybe because they are allowed to type simultaneously on one record. I have it set up right now where users can only edit and save their own records that they created. I use a Clerk Initial textbox that once they create a record it auto populates their initials in it and then they save it. so if they go back to their own record and try to save it again, it will check to match up their Sign-On initials textbox with the Clerk textbox and if it's a match it will allow them to save their changes. If another user looks at someone else's record and starts editing and then tries to save, it will do a check of their Sign-In and match it to the Clerk Initial textbox and if it's not a match it will undo everything they type and not let them save the record.

So with that being said, if the owner clerk is editing their own record and another clerk is looking at that same record and typing at the same time, I could imagine something like this freezing issue to get triggered. However, these users already know they can't edit another user's record so I don't think that is what is happening as it would be a wasted effort, but if it is, then I have an update in development that won't allow them to type on another person's record at all as I put some code in the On Key Up event so the check is a lot premature and the user can only get a keystroke or two in before the prompt comes up to stop them and undo it so I'm hoping that might fix the problem?

Any suggestions or thoughts on this is greatly appreciated. I will also update this thread once I get the error message info.

Thanks.

[Micron]

if it's not a match it will undo everything they type.

An ounce of prevention is worth a pound of cure. If you allow others to look at records that are not their own then best to
a) allow them to load records that are not theirs but as read only, or
b) allow them to load records that are theirs as read/write/edit.
In short, don't mix situations.

a) can be handled with form open code.

[Welshgasman]

Why would you let someone alter the data that they did not create, then undo it, instead of setting it to read only?

[data808]

An ounce of prevention is worth a pound of cure. If you allow others to look at records that are not their own then best to
a) allow them to load records that are not theirs but as read only, or
b) allow them to load records that are theirs as read/write/edit.
In short, don't mix situations.

a) can be handled with form open code.

I do like this idea, however there are several ways for the user to query records. I could probably use your "a)" suggestion when the user is fetching a single record at a time with a fetch form. They usually do a search using a split form with unbound fields at the top and datasheet on the bottom reflecting the records they search for using the fields at the top of the split form and then clicking on the record in the datasheet section to open the fetch form reflecting that single record.

As for the main form that they use for creating new records, I have buttons First, Previous, Next, and Last for the user to scroll through records. They also have a binocular button they can click on to do a "Find" record type of search by entering enter keywords in the whole document or a specific field. So being that the form is already open I guess I could make it do a check for the Clerk Initials and match it up to the Sign-On initials and if it's a match, use open form to allow edits and if its not a match then lock it up. Would that be sufficient? Would that not cause slow down since it's going to have to check each time they click on say the Previous button while scrolling through multiple records and changing the open form to either allow edits or lock it? Do you think this would prevent the freezing as well?

[data808]

Why would you let someone alter the data that they did not create, then undo it, instead of setting it to read only?

I created that part of the database when I first started back in 2014 and so a couple months ago I got bored and decided that I should go take a look back at some areas of the database and see if they can be improved. So that was one of the things that I wanted to change in this next update that I am working on. It's just a coincidence that I thought of this while updating this thread and that it might be the cause of the freezing issue.

If you guys think this might be the problem then I definitely want to take a look at it and fix it. My initial goal was to just make it so that the user doesn't waste time by typing out a whole bunch of information, only to find out they are not able to save the record because it's not their own. So if this fixes the freezing issue as well then that would kill two birds with one stone as they say and that would be great.

[Welshgasman]

Well it would not hurt to make anything the are not meant to change, but can view as read only. That is regardless of whether it fixes the freezing I would have thought?

[Micron]

Can't say too much about what might work with no database to look at. If there is something about the way records are accessed that prevents opening as read only, then I don't know. However, I don't think everyone should jump on this as being the cause and something that needs to be solved:

maybe because they are allowed to type simultaneously on one record.

You don't have specific feedback yet, and record locking settings should dictate what happens. What are your settings for this db?
AFAIK, even with no locks the db should not be "freezing up". Are you sure that what they're seeing isn't just the "not responding" message at the top? That does not mean the db is locked up.

[data808]

Can't say too much about what might work with no database to look at. If there is something about the way records are accessed that prevents opening as read only, then I don't know. However, I don't think everyone should jump on this as being the cause and something that needs to be solved:

You don't have specific feedback yet, and record locking settings should dictate what happens. What are your settings for this db?
AFAIK, even with no locks the db should not be "freezing up". Are you sure that what they're seeing isn't just the "not responding" message at the top? That does not mean the db is locked up.

Thanks for all the help on this. I applied one update a while ago that was to prevent the user from typing or editing records that did not belong to them. I added VBA to the On Up Key event and this checks their logins username to the clerk that created the record. If it does not match then it will prompt and undo their keystrokes immediately. After rolling out this update, I haven't heard anyone complaining about the db freezing up but that doesn't mean that it totally stopped. They could just be too busy to report back to me. Once I find out more then I will update you folks.

What are your settings for this db?

What did you mean by this? What settings are you referring to?

[NoellaG]

Just 2 things to consider:
1 when locking a record, it's not the record that is locked in Access but the entire page.
2 reading a record can also put a lock on that page.
3 when using SQL server or SQL express reading and writing rights can be set without one scrap of code. And the rights you set there are independant of the form you use to see/update the data.

[Micron]

What settings are you referring to?

Record locking settings.
https://support.microsoft.com/en-gb/...7-97fe0568019a