Database in inconsistent state error (1 Viewer)

[SAE]

I have an Access 2016 database on a server which has between 5-10 people using it at a single time. However ONE user and ONE user only keeps killing the database and getting the message "Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database....".

A compact and repair fixes the issue but it is becoming a pain as this can happen up to 10 times in a single day ... and then not for 3 weeks. The coding is correct as it works for ALL other users and generally for this one user, until their computer has a bad day and then it will happen each time they try and enter data.

Yes, the database is split and each user opens from a FE on their own C drive, with the BE being on the office server.

Any tips? As our IT departments swears blind that it isn't a hardware / computer issue and all users have all patches and up to date software and all other tips refer to the DB not being split or having multiple users opening the same FE.

 

[Mark_]

I would double check the users network connection.

Is this on a wireless network?

 

[SAE]

Sorry, forgot to add:

This user recently had a new computer with upgrade to Win 10 and the problem has only started since then. I develop the DB on Win 7. Dunno if there are any issues between the 2.

We all use Access 2016.

Network is by blue cable. Not wireless.

I queried the IT about their network connection or network dropping out and got a "Software Issue" in return.

 

[The_Doc_Man]

After you do Mark_'s checks, ask the user which form s/he is using and what function is being exercised. You are looking for differences in a sea of sameness.

If worst comes to worst, you might wish to institute a logging feature (if you haven't already) that logs what form a given user opens. A forensic log that doesn't have enough to let you rebuild a database can still hold enough info to help you debug a database. The thing is, you must somehow discover exactly what is being done.

While typing, it occurred to me that there is something else you need to consider. Do your forms DIRECTLY open to tables or do you have queries for all of your forms? My thought is this: You need to be sure that you do not have a user opening a table and then just sitting there. What you need is what I call a single-table query for those forms used to maintain single tables. Why? You can set the query to Optimistic locking, which minimizes the window of opportunity for a data-access collision.

You also need to assure that you minimize locking in other ways, but that is the biggie.

 

[SAE]

@ The_Doc_Man. All forms are opened with queries. Generally I have filter fields on a main form that then filters the data shown on a sub-form. This is the case with this form too.

 

[Mark_]

For this user, which version of ACCESS is installed? 32 bit or 64 bit?

 

[SAE]

@ Mark,

Everyone has Win 64 bit - for the extra memory - and Office 32 bit for compatibility. Windows is a mix of Win 7 with the newer computers being on Win 10. This user is Win 10.

Previously a 32 bit compiled DB wouldn't open on a 64 bit office computer and vice versa. Dunno if Access still works like that but everyone in the office uses Access 32 bit.

 

[Pat Hartman]

Are all the users opening the very same copy of the FE? If so, you need to change this sooner rather than later. If the database is not split, that is the first step to separate the forms/reports/queries/macros/code into the FE (Front End) and leave only the tables in the BE (Back End). The BE remains on the share directory on the server. The FE is distributed to each user so each user has his own personal copy that he runs from his c:\ drive. You can use a batch file to distribute the FE or you can use one of the several free databases that will facilitate the distribution.

Once the database is split and every user opens a separate copy of the FE, you will dramatically reduce the potential for corruption or problems like this one.

If it is only a single user, there is something wrong with their set up, check their network connection. Access cannot recover from dropped connections and since eveyrone seems to be sharing the same physical copy of the file, if this happens to one user, it breaks the app for all users.

Keep in mind, that when an Access app opens, parts of it are loaded into memory on the user's computer and Access periodically saves changes back to the hard drive. There is lots that can go wrong with this scenario.

 

[Mark_]

Yes, the database is split and each user opens from a FE on their own C drive, with the BE being on the office server.

@Pat,

He's already split and running individual copies. This does seem to be machine specific.

 

[isladogs]

Try decompiling the FE database on the affected machine
See http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

 

[The_Doc_Man]

This might take a bit of doing as it might require you to "undo" the user's security a little, but you want to verify that the unfortunate user's References are complete.

Also, you kind of glossed over this point a little, so I'm directly asking just to confirm. Your unfortunate user is not the ONLY Win 10 user, right? Just the only one to have the problem, right?

There is another issue and I would not want to point this way except that it is kind of necessary in order to eliminate some things. Two machines are involved here - the FE on the unfortunate user's Win10 system and the machine acting as the BE file server. Advise your unfortunate user that if he gets another one of these events, IMMEDIATELY check the system clock, normally shown in the lower right in the task bar. Better still, if there is a clock "gadget" on-screen, note the time of day to the second.

Then even if you need help from the network guys, review the system, application, network, and system error logs on both machines for that time of day, plus or minus no more than about a minute. It would be rare to have more than a couple of entries including timestamps in any of those logs, so if you see something close to the right time, that might be a clue.

See if the FE machine exhibits an MSACCESS.EXE call-out. I'm not 100% sure what the call-out would be on the BE file, but it would be a file-related system program.

I freely admit this is a long shot, but it is time to dot some i's and cross some t's here to get this figured out, and eliminating anything that a system log would capture will narrow the field of places to look. Narrowing down the field is a good thing even if you find nothing in any of the logs.

 

[SAE]

@Doc_Man,

Yes, we have about 100 computers in the office (not all DB users) and just got in 10 new computers running Win 10. I develop and compile on a Win 7 computer. All users have 64 bit Windows and 32 bit office.

About half of the new Win 10 users run the DB and only 1 is getting issues. This user never had issues when they had their old computer running Win 7 from the same desk / network plug etc.

I have reported to IT to check this users computer / network card / cable / anything, and get fobbed off that it is a software issue.

All users run a compiled accde file. I will give this user a accdb version to see if there is a problem in the compiling that their computer doesn't like.

But going on responses to date I feel more secure that this isn't a coding issue or a kink introduced by Win 10.

 

[CJ_London]

back on the first post

A compact and repair fixes the issue

of which? the front end .accde or the back end .accdb? Just struggling to understand what a C&R does for a .accde - does it contain some local tables? And have you tried Ridders suggestion of decompiling before creating the .accde?

also, just to confirm the hardwire - is this a laptop? It's just I had someone with a laptop, hardwired at his desk- except when he took it to a meeting.

 

[SAE]

@ CJ,

Thanks for the reply.

The DB hangs and the FE seems not to be able to read the BE DB anymore. The FE opens but the login on the first form has an empty drop down where you select the project. So the FE DB will open and open the first form but can not read data from the BE.

Upon opening the BE I get the bad DB error and I need to do a C&R on the back end to fix the issue. The C&R usually fixes it but this weeks error meant the BE DB was totally dead and i had to do a restore from the mornings server backup and email out everyone to redo all of their work for the day. Not fun ...

All users have desktops except me who works in the office, and at home, and on the train ...

 

[The_Doc_Man]

It is far from the only way that corruption ever happens but... if someone has a system crash while in a database, the next user of said database will potentially flop flat on his face. Ask around if OTHERS have been rebooting their machines or having crashes while the database is open in a window even if they WERE NOT acting inside that window. In other words, their copy was open but quiescent at the time. You see, you told us that the unlucky person can go three weeks without so much as a hiccup but then BANG ZOOM and you have to step in. So it might not even be your software. See if any of that is going on, and if so, try to correlate it to your unlucky person. Though if there IS anything going on like this, I would have bet it wouldn't ONLY affect one person.

Be that as it may, you should cover the bases. Broach the subject with your IT people that you have two machines, one of which works and one of which doesn't. Tell them you agree it isn't hardware, but it MIGHT be a couple over other things.

For instance, there are the domain's config files that are applied when the machine boots up. Ask the IT folks to verify that both machines use the same domain profile files.

There is also the matter of how the machines were built out, since your shop sounds like it is big enough for this kind of work: Ask the IT folks to verify that both machines were cloned from the same master disk, or however else they build out the machines at your site; we used a package called Ghost to identically configure our machines.

Also, is there something unique about the app sub-set that Mr. Unlucky uses? Like is there some form that only this person uses? You are looking for differences so you have to take in both the likely and the unlikely sources of difference.

If there are no errors in the hardware, does it have the same amount of memory as the working systems? Is it on a separate network segment from everyone else and if so, is it behind a "smart" sub-net firewall? Does this person have occasion to open an Application Object that isn't based on Access? Like Excel or Word?

 

[Pat Hartman]

Is the unlucky user now running with an .accdb copy of the database?

Is everyone using the full version of Access or are they using the runtime?