Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-15-2018, 03:18 PM   #1
dmyoungsal
Newly Registered User
 
Join Date: May 2016
Posts: 112
Thanks: 10
Thanked 2 Times in 2 Posts
dmyoungsal is on a distinguished road
Incosistent State

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?

__________________
Thank you,

Mark Young
Passionate about Bianchi biciclettes
dmyoungsal is offline   Reply With Quote
Old 05-15-2018, 03:28 PM   #2
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Incosistent State

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/microsoftacces.../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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 05-15-2018, 03:41 PM   #3
dmyoungsal
Newly Registered User
 
Join Date: May 2016
Posts: 112
Thanks: 10
Thanked 2 Times in 2 Posts
dmyoungsal is on a distinguished road
Re: Incosistent State

Quote:
Originally Posted by ridders View Post
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/microsoftacces.../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.

__________________
Thank you,

Mark Young
Passionate about Bianchi biciclettes
dmyoungsal is offline   Reply With Quote
Old 05-15-2018, 03:45 PM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Incosistent State

Quote:
Originally Posted by dmyoungsal View Post
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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ridders; 05-16-2018 at 03:14 PM.
ridders is offline   Reply With Quote
Old 05-16-2018, 06:47 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,945
Thanks: 13
Thanked 1,320 Times in 1,259 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Incosistent State

With each person running his own copy of the FE, the problem could go away entirely or it might resolve to only single user.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-16-2018, 02:07 PM   #6
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,604
Thanks: 41
Thanked 1,021 Times in 931 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Incosistent State

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hello from Washington STATE Kerry Introduce Yourself 0 01-17-2014 11:51 PM
Hello all from NY state thesavo Introduce Yourself 3 12-30-2011 04:41 AM
Question DB state? spinkung General 5 08-07-2009 03:22 AM
Question Help, I'm in a State! HowieP General 4 01-27-2009 06:56 AM
Left Join query on form shows null state on chkbox and blank state on query Fernando Forms 7 02-22-2008 12:30 PM




All times are GMT -8. The time now is 09:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World