Multi-User Split Access Database Freezing

[data808]

I have a split database with a front end and multiple backend tables that the front end saves records to. Occasionally the front end will freeze for one of the users while trying to save a record and so in order to get it going again we have everyone log off and then log back in and that seems to fix the problem. However, it's been reported to me that this has been happening a little more frequently.



Is there something I can do to test to see what is causing this issue? We are on Windows 10 and using MS Access 2013 if that helps. If you need more details, please let me know. Thank you.

I should also add that we have around 25-30 users logged in so I'm wondering if that is the issue and I need to migrate to a server?

[orange]

Does each user have their own copy of the FE on his/her computer?

[data808]

Does each user have their own copy of the FE on his/her computer?

Yes each user has their own copy of the frontend file. The backend is on a network drive.

[orange]

Are all connected to the network via cable vs wifi?

[data808]

we are all connected through cable. they don't allow us any wifi access.

[Micron]

The same user experiences this issue, or it could be anyone? Or it can be a few of several, but it has always been the same few? The latter one would take several occurrences to figure out. I think it's virtually impossible to figure this out without some time and accurate assessments. People need to report on what they were doing at the time, but often the info they can offer is not real helpful because they don't notice/remember details.

It could be that the server is just busy and Access is in a queue. It could be related to record locks. Do they see any system messages (e.g. "not responding"). How long does anyone wait until deciding they need to do something other than wait? Is the file bloated?

[data808]

The same user experiences this issue, or it could be anyone? Or it can be a few of several, but it has always been the same few? The latter one would take several occurrences to figure out. I think it's virtually impossible to figure this out without some time and accurate assessments. People need to report on what they were doing at the time, but often the info they can offer is not real helpful because they don't notice/remember details.

It could be that the server is just busy and Access is in a queue. It could be related to record locks. Do they see any system messages (e.g. "not responding"). How long does anyone wait until deciding they need to do something other than wait? Is the file bloated?

Thank you very much for your insight. What you are saying makes total sense. I anticipate that this may take a while to get all the information needed so bear with me as I keep updating this post as the information comes in to me.

To answer the first question, I couldn't tell at the moment because I'm not sure which user is the one to cause the freezing issue but the person complaining about this behavior is usually random. This also could be too late if the database is freezing first by one user and then the next user is the one to complain about it. I also agree with you on the user may not remember specific details on what happened right before the issue occurred and so the information would be inaccurate or incomplete.

As for record locks, I am interested in looking into that as I have heard about this before but know nothing about it. I will start trying to see if they receive any error messages and post it here if I find out anything. It may take awhile as I do not work in their department and the freezing doesn't happen all the time. In the meantime, if you could give some assistance with the record lock idea, I would appreciate it. Let me know what I need to check.

Thanks.

[Micron]

I have no specific links to offer but I'd say if you google
ms access record locking
you will find lots of rabbit holes that you can go down into.

[moke123]

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.

[NoellaG]

Hi, if you have 25-30 simultaneous users I would advise to upgrade the back-end to a database system like SQL server or MySQL. A database system can not only handle locking much better, but is not as likely to get corrupted, has better back-up systems, can handle more data, ect. Downside: you have to spend some time to learn how to manage the system.

[data808]

I have no specific links to offer but I'd say if you google
ms access record locking
you will find lots of rabbit holes that you can go down into.

Thanks for the advice. Is record locking set by default? Or would I have to manually set that up myself?

[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.

Chinese characters within the backend file as in one of the records containing Chinese characters or the backend file name itself containing Chinese characters? The backend file name itself has no Chinese characters. I haven't looked inside the backend file to see if there are any Chinese characters in any of the record data.

The file size of the backend is 92,164KB and I heard it can reach a limit of 2GB so I think that part we are doing ok. As for the compact and repair, I usually only do this when I actually make changes to the tables in the backend like adding new fields and what not. I do compact and repair the frontend a lot more frequently. Usually with every update I release.

I do pretty frequent backups of the backend files. Usually at least once a week.

[data808]

Hi, if you have 25-30 simultaneous users I would advise to upgrade the back-end to a database system like SQL server or MySQL. A database system can not only handle locking much better, but is not as likely to get corrupted, has better back-up systems, can handle more data, ect. Downside: you have to spend some time to learn how to manage the system.

This is something that was brought to my attention in the past but yes, I would have to learn how SQL servers work and I'm not sure if my IT department would allow me to implement something like this. Especially if money is involved to get it set up.

[Micron]

Is record locking set by default? Or would I have to manually set that up myself?

Can't remember. Between me and Google, we know pretty much everything though.

[ArviLaanemets]

This is something that was brought to my attention in the past but yes, I would have to learn how SQL servers work and I'm not sure if my IT department would allow me to implement something like this. Especially if money is involved to get it set up.

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).