Incosistent State (1 Viewer)

dmyoungsal

Registered User.
Local time
Today, 11:08
Joined
May 1, 2016
Messages
112
I have a database that we have been using for about three years. The DB consists of a front-end and a back-end. I have about 30 users that share the front end using a shortcut. (Again, this has worked like a charm for three years). Each user has Acess Runtime installed on their computer.

Just recently, when users open the DB, they are getting an "the DB has opened in an inconsistent state" or "The last time you opened the DB, you caused errors."

I can solve it, by getting everyone out and doing a compact/rebuild, but it shows up again in the next day or so.

What would cause this to happen all of a sudden and what is a quick solution?
 

isladogs

MVP / VIP
Local time
Today, 18:08
Joined
Jan 14, 2017
Messages
18,186
You have a disaster waiting to happen & what you have now is just the start...
You are VERY lucky its taken 3 years to start to go wrong

Users share the network backend file but should NEVER share the same front end
Each user needs their own copy of the front end on their own PC

It sounds like you may have corruption in the VBA code

First make a backup of the front-end just in case
Suggest you decompile the front-end, then recompile and finally compact.
See http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

Now make another backup of the new front-end
You will then need to distribute the new front-end to all users as a matter of urgency
AND replace the existing shortcuts with one for the FE on their own PC
 

dmyoungsal

Registered User.
Local time
Today, 11:08
Joined
May 1, 2016
Messages
112
You have a disaster waiting to happen & what you have now is just the start...
You are VERY lucky its taken 3 years to start to go wrong

Users share the network backend file but should NEVER share the same front end
Each user needs their own copy of the front end on their own PC

It sounds like you may have corruption in the VBA code

First make a backup of the front-end just in case
Suggest you decompile the front-end, then recompile and finally compact.
See http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

Now make another backup of the new front-end
You will then need to distribute the new front-end to all users as a matter of urgency
AND replace the existing shortcuts with one for the FE on their own PC

what would happen if I made a copy of the FE for each user on the network so the effect is each user is running their own copy.
 

isladogs

MVP / VIP
Local time
Today, 18:08
Joined
Jan 14, 2017
Messages
18,186
what would happen if I made a copy of the FE for each user on the network so the effect is each user is running their own copy.

It would be better than the current setup but far from ideal
Performance would be affected by the FE being on the network & you would have a greater chance of corruption caused by network outages.

What I described is the standard solution for all split databases in a multi-user environment
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:08
Joined
Feb 19, 2002
Messages
42,971
With each person running his own copy of the FE, the problem could go away entirely or it might resolve to only single user.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:08
Joined
Feb 28, 2001
Messages
26,999
If the network's performance is acceptable, having a unique but remote FE for each user might work OK. If the performance is marginal, you do better by having the FE located on each user's computer (individual copies).

The reason for the performance difference is that all the work is done on the local machine in MSACCESS.EXE or whatever is the Runtime executable file. No matter where the FE and BE are located, the work is done ON the local machine. Except...

MSACCESS.EXE uses the host of each file (whether FE or BE and whether remote or local) as a file server. When you open a file on a system, you take out file locks. The system hosting the file opened in that circumstance becomes the lock manager for that file. When multiple users associate to a file they must communicate to the file's host to get permission to use the file (access arbitration followed by lock arbitration).

If the FE is local, the locks are local and NOBODY but that user "sees" them. In essence, lock arbitration is "Does anyone else have this file open?" "No!" "OK, use it." Takes at most a few microseconds because everything is local.

You can guess that it gets complex for the shared BE because unless you are the first person to open the file, the BE file's host has to check the locks from every other user AND it is a network operation, not a local operation. That arbitration takes multiple milliseconds, usually. OK, not a lot of time. But it adds up when you have a lot of users and it adds up even faster if the network has at least one slow link in it. On a really laggy network, remote lock arbitration can really start to slow you down.

This is by far not the only problem that would cause slowdowns, but on a poor network it can be a significant drag on speed. Which is why you want every FE file local if it CAN be local.
 

Users who are viewing this thread

Top Bottom