What's this madness about Access? (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Feb 19, 2013
Messages
16,553
Handy for me to click one to another instead of opening and closing screens
that's where popups or using form instances come in handy
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Feb 19, 2013
Messages
16,553
@KitaYama - you might be interested in this link - attempts to prevent users from making silly mistakes
 

KitaYama

Well-known member
Local time
Tomorrow, 06:23
Joined
Jan 6, 2022
Messages
1,489
@KitaYama - you might be interested in this link - attempts to prevent users from making silly mistakes
Will read it as soon as I'm back home.
Thanks.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
An access database is a tremendous thing. It maintains multiply indexed tables, and pointers to remote multiply indexed tables. It manages to extract data from each of these tables, blindingly quickly, with interactive queries and so on. It even manages to do all this for multiple concurrent users, although we advise again this.

In the same single file, it also allows you to build forms, reports and other objects.

All of this is held in a single massive monolithic file structure, and rarely fails. I find it completely impressive to be honest.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
Just search for Corruption on AWF.
If so many newbies can break their apps, it definitely means Access has some mayor problem not to prevent the conditions those newbies have made.
2 users can not open the same Excel file at the same time. If multi user on the same FE is the problem why Access doesn't behave like Excel?
Why different users are allowed to open the same FE? Isn't it a mistake in Access design?

Again in my case we don't have multi user on the same FE.

Here's some musings

No, but you still have multi-user on the same back end. Access does allow shared front ends. However, having a shared front-end means you can't save temporary data in tables within that front end, as concurrent users could interfere with that temporary data. So sharing front ends affects design choices. We have just found it safer to recommend not to use shared front ends. That's all.

You also do need a multi user app. If an office accounting system worked like Excel so only one person at a time could use it, it would be usable only for a very small environments. We get loads of questions about users wanting to use onedrive as a solution for a shared database. No. Onedrive works, like excel, as a serial single user environment.

So think about a database. Any office has multiple people working on shared data concurrently. The shared data manager (the programme not the person) has to manage access to that data so that data does not get corrupted, that deadlock issues are resolved, that resource contention is handled. You also need sophisticated queuing management. You don't want 10 users requesting query results to wait in turn to get their query results. You want the database manager to allocate CPU time in a shared fashion so that each user gets a decent response. So you need some sort of priority queue managing shared resources. You also need transaction control, so that in some cases a user's process can be rolled back and cancelled, while at the same time allowing other users to complete their processes. You need sophisticated record locking techniques to manage updates to records. You need index structures that allow massively large files to be manipulated in reasonable times. You need sophisticated working set and page handling techniques that allow active data to be swapped in and out with inactive data to achieve decent response times, without degrading to thrashing, so that multiple users can share a modest CPU environment.

It's also not like Excel where you can just decide to not save changes. In a database all changes are immediate and permanent. Other users use your data instantly, so in general it's not so easy to undo data that has been written and saved. There are so many ways to save data in a database, that it's actually quite difficult to prevent data being saved.

Finally, it's also not like the "web" where you can just crash an app that's not responding and start over. You can't safely crash a database without considering the state of the data.

It's often not so much corruption that causes problems. Bad database design allows unexpected problems to proliferate and maybe even not be detected. You can get a decent excel worksheet without ever using code. You can't get anything decent in Access without using a lot of code. Much code is defensive and is there to restrict what users, including the developer, can do. It's just so easy in a database to catastrophically damage data without intending to, because databases, and queries are so very powerful.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
If so many newbies can break their apps, it definitely means Access has some mayor problem not to prevent the conditions those newbies have made.

Not necessarily. There is an old saying I learned long ago... artificial intelligence cannot overcome natural stupidity. (That might sound a bit harsh, but...)

The people who so easily crash things they built are the people who don't bother to learn how to correctly use, design, or implement shared applications. They are like people who jump into the deep end of the swimming pool and then wonder how they have suddenly gotten in over their heads. The idea that Access is a rapid application development tool doesn't stop it from having issues of importance to consider. The part that Access makes easy isn't the most important part. A bad design will hobble or outright break ANY database environment.

Earlier, a "car" analogy was used about how manufacturers have to improve safety of the vehicle. But there is no improving the safety of a vehicle being driven by a drunk, angry, callous, uncaring individual. On YouTube you can find videos of police chases where the fleeing suspect reaches speeds in excess of 100 miles per hour (160 km per hour).

What do you do about a failing project when someone arrogantly says "I will make this work because I know all about coding, we don't need to waste our time on designing anything"? The truth is that coding is between 30% and 35% of most projects of any magnitude or importance. If you don't have at least 40% of your project set aside for detailed design and analysis, you have already screwed up badly.
 

KitaYama

Well-known member
Local time
Tomorrow, 06:23
Joined
Jan 6, 2022
Messages
1,489
Earlier, a "car" analogy was used about how manufacturers have to improve safety of the vehicle. But there is no improving the safety of a vehicle being driven by a drunk, angry, callous, uncaring individual. On YouTube you can find videos of police chases where the fleeing suspect reaches speeds in excess of 100 miles per hour (160 km per hour).
@The_Doc_Man I hate to take out only one part of a well-explained answer and reply to that section only. But just to answer there's always one way out of a disaster.
All cars soled in our country has a speed limiter. Without a speed limiter no manufacturer is allowed to sell a car. And it's against the law to remove the limiter. It's still not common , but there are several cars that come with cameras for recognizing facial expressions, sensors that detect heart rates and software that assesses a driver’s state of awareness. We're not still there but at least there's a movement to prevent a disaster. But how much Access has put to prevent what may happen?

About the rest of your reply I only can say this. I don't consider myself a developer, nor an experienced programmer. But I think my bad coding or my lack of knowledge MAY cause a crash. But not a corruption. Because I believe a software can be designed to catch an error and shut itself down before being destroyed. That is the whole point of a crush. With your experience, I'm sure you have seen the blue screen of death. But it has been in win 95 or xp. How many time you have experienced it in win 10 or 11?
In MY case, never. That is development. They catch what may happen and prevent it in later releases. And for sure, I don't see this development in Access. There are new features and tools in recent versions, but the problem is still there.
Unfortunately it seems for most of you this is not a problem. It's a part of the nature of a database to corrupt.
 
Last edited:

KitaYama

Well-known member
Local time
Tomorrow, 06:23
Joined
Jan 6, 2022
Messages
1,489
@gemma-the-husky
Thanks for taking your time and explain in detail.
I must think a little about what you (and others) explained about the nature of a database.

I don't have any experience outside Access. Are other RDBMS the same as Access? (in terms of corruption).
Does what you explained justify a FE's corruption?

I admit that I had to use accde as it's suggested earlier.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
Unfortunately it seems for most of you this is not a problem. It's a part of the nature of a database to corrupt.

You have isolated on something but failed to recognize that it is a "can't see the forest because of all the trees" situation. You focus on Access but fail to see that it is inherently a database problem first.

We have to work with the tools we have and some of them have more to do with Windows than with Access. If someone decides to crash Access by stepping to the Task Manager and doing an "End Task" on an Access session, that will almost immediately cause a problem. But Access can do NOTHING about this, and neither could Paradox, ORACLE, Ingres, SQL Server, ... any DB utility. Users are the absolute masters of their sessions (whether they are admins of the system or not). When a user says "Shut down my task" then Windows freezes execution of the task and initiates whatever is in the task for shutdown protocols. When the user says "Log out my session" then Windows freezes every task activated at the time of that logout. In the final analysis, there are things that a user can do that Access can NEVER guard against because it has insufficient time. It takes TIME to roll back a query. It takes time to close connections. But when Windows is going to shut down NOW, there is nothing you can do because you have no time left. This failure will occur in ANY SYSTEM where the front-end has multiple interactions to manage because the FE has to commune with the BE multiple times.

Things that fall in this problem category are power outages, system crashes, user shutdowns, user logouts, network drops, and users who should never have been given a computer in the first place because they are chowderheads. NOT talking about you, KitaYama. But you must know at least a few folks I COULD be talking about.

Remember that my definition of corruption includes a "blurred image" where the blurring is induced by the fact that computers don't operate instantaneously (no matter how fast they SEEM to operate.) Any database utility you name will have the same problem at some point. One of those "blurs" will occur when the workstation dies, crashes, logs out, powers down... without completing some step. At that point, you have an incomplete update no matter WHICH utility is writing it. And that incomplete update is the source of corruption.

In fact, Access HAS the ability to protect against some of this by making transactions using the "BEGIN TRANSACTION" / "COMMIT" sequence. But the architecture of Access is such that even THIS can fail for native Access back-ends, since the transaction is a workspace concept and that means it is still managed by the front-end system. And it is the front-end's catastrophic failure that causes the damage. Using SQL server or one of the other active DB engines, you can at least back out or commit transactions if needed, even if the front-end dies after issuing the COMMIT or CANCEL call. And Access CAN use these active back-end utilities. So I fail to see that Access deserves quite the disdain that you are sending to it.

Yes, it IS part of the nature of a DB to become corrupted. But we are great followers of the Serenity prayer. We need the skill to prevent the problems we can prevent, the grace to accept the problems that we cannot prevent, and the wisdom to know the difference.
 

KitaYama

Well-known member
Local time
Tomorrow, 06:23
Joined
Jan 6, 2022
Messages
1,489
@The_Doc_Man
I really appreciate your detailed explanation. But for sure you understand I'm not complaining about a corrupted database after a user terminated the application while read/write data or electricity shortage or windows reboot while an action is in progress.

Again I feel being a broken record but I have seen so many "corrupted Access database" posts in Access communities and I was wondering how easily an Access file MAY get corrupted.
I was given several reasons. Shared FE, Multi user, not split databases, using accdb file type as Fe. Even I was given a list of important rules in managing a database (#17).
To be true, while I understand everyone of you are correct, I also believe many of those points could have been impediment in Access source code. If multi user on the same FE MAY cause a damage, well, it's easy not to allow opening a second instance of the same FE in read/write mode (just like Excel). They also can change their code not to allow running a not compiled file, or prevent compacting it over a LAN network, or prevent editing the code in break mode.

I also was told there are situation a developer needs to do something and if they close all the doors, developing may be hard. Just like when I need to start the engine of a car, but there's no need to check for seatbelt. While it's true, there can be an option to put the database on developing mode or running mode.

Yes, it IS part of the nature of a DB to become corrupted. But we are great followers of the Serenity prayer. We need the skill to prevent the problems we can prevent, the grace to accept the problems that we cannot prevent, and the wisdom to know the difference.
well, I think it sums it up. If it's because of how a database behaves, there's nothing more to say. I try to keep the rules in mind.

Edit : I replaced all accdb FE files with accde. Hope it prevents corruption.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
It's very difficult to debug modern event driven programming.

First, the programme isn't sequential. You could design your database so that a user can only do one thing at a time. Open a form, and not be able to open any other from until that form is closed. That would make it easier to control what happens., but it might not provide a great experience. You could also design your programme not to be multi user. As soon as you allow more than one user to use a programme at the same time, and each user to use the programme in a "random" manner, you must lose control over the programme. You can't manage every possible combination of circumstances that might occur during the programme use. Some circumstances may cause the program to fail. If you are lucky an error will occur and you will be aware of it. If you are unlucky you might not be aware of it.

For instance, there seems to be a current issue where users are getting a "too many databases" error. This is almost certainly a windows issue, or an MS Office issue. It's very unlikely to be a coding or database issue. But we can't be sure what the consequences of that problem are. The error may have already caused a data corruption that won't become apparent until later. Not all users are getting the error, so the error isn't "just" to do with access. Maybe it's to do with a particular build of access on a particular processor , Add to this problems caused by wireless interfaces, coding errors, windows issues, interaction between different programmes you are running, hardware drivers, and so on. A data corruption may be caused by some unusual combination of circumstances. In fact, it must do, because nobody designs an application to malfunction.

It's not quite like a game, but in some ways it is. These multi-level multi player games allow players to play games in circumstances that the designers cannot have imagined, and as a result some stray bug can cause a game to "glitch" or malfunction. A phone can freeze at some point because of a completely unexpected scenario. We know some circumstances can actually permanently "brick" a phone. Access is like this in many ways, and is probably even more sensitive when the code and data is not split.

A "glitch" in a multi-user mission critical database is rather different to a "glitch" in a game, but it's all part of same general problem.

MS are perpetually releasing windows and office updates as a result of problems that surface from time to time. Some of those fix problems. but some cause unexpected problems. Maybe this is the source of some "corruptions".
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 14:23
Joined
Nov 25, 2004
Messages
1,776
I came across one client where the boss opened the program, moved to a screen, then opened another etc, etc. When I saw her PC she had 16 instances of the program across the taskbar. "Handy for me to click one to another instead of opening and closing screens" she said. She left the PC on 24 hours a day and before the weekend, no doubt just switched off, without closing them all. She claimed she closed them all first........and we can all definitely believe that can't we? I had to make changes to prevent her, or anyone else from doing that. Just never imagined someone would do it.
In our recent AUG user group meeting the presentation included a demo of a technique to spawn copies of a form to handle a scenario somewhat similar to what you describe. The form, of course, would be identical so that it wouldn't support different parts of an application, but it could allow a person to see, for example, five different customer's records on five copies of the customer screen, or four different orders on four copies of the order screen, etc. I think Juan plans to make the accdb sample available. Details are in the YouTube video.
 

GPGeorge

Grover Park George
Local time
Today, 14:23
Joined
Nov 25, 2004
Messages
1,776
Not necessarily. There is an old saying I learned long ago... artificial intelligence cannot overcome natural stupidity. (That might sound a bit harsh, but...)

The people who so easily crash things they built are the people who don't bother to learn how to correctly use, design, or implement shared applications. They are like people who jump into the deep end of the swimming pool and then wonder how they have suddenly gotten in over their heads. The idea that Access is a rapid application development tool doesn't stop it from having issues of importance to consider. The part that Access makes easy isn't the most important part. A bad design will hobble or outright break ANY database environment.

Earlier, a "car" analogy was used about how manufacturers have to improve safety of the vehicle. But there is no improving the safety of a vehicle being driven by a drunk, angry, callous, uncaring individual. On YouTube you can find videos of police chases where the fleeing suspect reaches speeds in excess of 100 miles per hour (160 km per hour).

What do you do about a failing project when someone arrogantly says "I will make this work because I know all about coding, we don't need to waste our time on designing anything"? The truth is that coding is between 30% and 35% of most projects of any magnitude or importance. If you don't have at least 40% of your project set aside for detailed design and analysis, you have already screwed up badly.
I can't remember where I first heard this, but one of my all-time favorite taglines has been, "When I said I built it to be idiot-proof, I had no idea there were that many idiots out there."
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
I can't remember where I first heard this, but one of my all-time favorite taglines has been, "When I said I built it to be idiot-proof, I had no idea there were that many idiots out there."

One day a LONG time and at least ten employers ago, we were looking to hire, the day was getting long, and we were getting shell-shocked by the uniformly poor quality of our candidates. Somehow the discussion wheeled around to the idea of hiring someone to test our programs to make them foolproof, but then we speculated on the want-ad it would take.

Someone said, "I know, we will run an ad that says 'Wanted, perfect fool to test fool-proof programs" and that started the giddiness. The next comment was "What fool would apply for such a job?" Then "What fool would think he qualified?" Followed by "What fool would HIRE someone who qualified for that job?" Then "What fool of a manager would allow such a person to touch one of his projects?" It devolved into "fool" jokes for another ten minutes before the mood wore off. But during that time, we decided that the fool would write documentation in calligraphy on foolscap (a type of paper). We also talked about the uniform that included pointy-toed shoes with small bells here and there, and a three-pronged floppy hat.

We were CONSTANTLY finding ways that folks could crash our systems. We put in safeguard after safeguard. Then one day one of our better customers sent a "supervisor of ops staff" - the guy who was the boss of the folks who would actually use the equipment. He said, "We just found a new test for you" and sat down on one of our customized keyboards. Crashed our keyboard monitor in a heartbeat due to a buffer overflow. The supervisor told us it wasn't unusual for the night operators to take a nap with their head on a keyboard in a way that triggered auto-repeat. Our next version was very careful about buffer overflow events. But honestly - how likely is it for a normal person to sit on a piece of computer equipment like a keyboard?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
Again I feel being a broken record but I have seen so many "corrupted Access database" posts in Access communities and I was wondering how easily an Access file MAY get corrupted. I was given several reasons. Shared FE, Multi user, not split databases, using accdb file type as Fe. Even I was given a list of important rules in managing a database (#17). To be true, while I understand everyone of you are correct, I also believe many of those points could have been impediment in Access source code. If multi user on the same FE MAY cause a damage, well, it's easy not to allow opening a second instance of the same FE in read/write mode (just like Excel). They also can change their code not to allow running a not compiled file, or prevent compacting it over a LAN network, or prevent editing the code in break mode.

Again, you are looking at this, not with your eyes open but rather with some level of squinting.

I had a 40-user database, split native FE/BE, users had their own individual copies of the FE, FE was NOT an ACCDE, typically 4 to 8 users actually logged in at a time, and the only time it ever corrupted in five years was when one of my previously enumerated user brain-cramp moments happened, or when we had a network or power event. Invariably, our corruption problems were due to operational procedures, not faulty code, because I tested the bloody hell out of everything I published.

The U.S. Navy had a personnel operation for BUMED (the Naval Bureau of Medical Personnel) that managed scholarships and stipends for medical students who enlisted in the Navy. It was an Access FE, SQL Server BE, and it NEVER corrupted that I know of, even though most corruption is due to FE errors.

I will say it again. Blaming Access for excessive corruption ignores the problem that the application may have been set up in a way that invites poor programming or operational practices. Is Access perfect? No, no, a thousand times no. Does Access cause corruption? It can - but most of the time it is NOT the cause. Poor procedures and poor practices are the usual cause.

You are asking Access to protect you from your own foibles, but I don't know of many systems that actually do that. In my opinion, you are looking for Access to rescue you from your own human imperfections. In my opinion, I would never WANT to program in an environment that thinks it knows better than I do how to program a solution for a given problem. At that point, why would I even be there?
 

GPGeorge

Grover Park George
Local time
Today, 14:23
Joined
Nov 25, 2004
Messages
1,776
Again, you are looking at this, not with your eyes open but rather with some level of squinting.

I had a 40-user database, split native FE/BE, users had their own individual copies of the FE, FE was NOT an ACCDE, typically 4 to 8 users actually logged in at a time, and the only time it ever corrupted in five years was when one of my previously enumerated user brain-cramp moments happened, or when we had a network or power event. Invariably, our corruption problems were due to operational procedures, not faulty code, because I tested the bloody hell out of everything I published.

The U.S. Navy had a personnel operation for BUMED (the Naval Bureau of Medical Personnel) that managed scholarships and stipends for medical students who enlisted in the Navy. It was an Access FE, SQL Server BE, and it NEVER corrupted that I know of, even though most corruption is due to FE errors.

I will say it again. Blaming Access for excessive corruption ignores the problem that the application may have been set up in a way that invites poor programming or operational practices. Is Access perfect? No, no, a thousand times no. Does Access cause corruption? It can - but most of the time it is NOT the cause. Poor procedures and poor practices are the usual cause.

You are asking Access to protect you from your own foibles, but I don't know of many systems that actually do that. In my opinion, you are looking for Access to rescue you from your own human imperfections. In my opinion, I would never WANT to program in an environment that thinks it knows better than I do how to program a solution for a given problem. At that point, why would I even be there?
I wish I could state the situation that well. As a solace, I think I'll resort to quoting you extensively.

In my more than twenty five years working with Access, first as a power user and later as a paid consultant, I can think of one, and only one case of corruption in an mdb or accdb in which I deployed a relational database application. It turns out that one user who took over an application I'd created had decided to open the mdb with Word, just to see it it could be done, or some such thing. He then saved and closed it, transforming the internals to Word mush. Access fault? Well, no, although it is true that Windows let him do that. My fault? Well, partly, as it wasn't split as it should have been. The user's fault? Well, partly, but only partly. He was naïve but thought he knew more than he did. The one thing I can say for sure is that there is no way to program sufficiently against users who don't know what they are doing.

I was called in once as a consultant to help deal with a corrupted mdb BE. Their flakey network was subject to frequent drops. It was old, cobbled together and roughly maintained. So a bad write during one of their frequent network blips, and boom. At least two or three records were borked. Again, was that Access fault? No, although it is true that Access is vulnerable to such network problems. Was it the users' faults? No, they were doing what they did all day every day. Was it the original developers fault? Not really, no. I doubt there could have anything done differently to avoid the impact of bad networks. A more robust back end, SQL Server or SQL Azure, would be less likely to corrupt in that situation. Still bashing Access over it is simply inappropriate, IMO.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:23
Joined
Jan 20, 2009
Messages
12,849
When I said I built it to be idiot-proof, I had no idea there were that many idiots out there."
My favourite version of this is, "Nothing can ever be fool-proof because fools are so ingenious."
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:23
Joined
Jan 20, 2009
Messages
12,849
. It turns out that one user who took over an application I'd created had decided to open the mdb with Word, just to see it it could be done, or some such thing. He then saved and closed it, transforming the internals to Word mush.
Now that is true fool ingenuity.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
In addition to testing for foolish behaviour, there is a corollary where skilled and intelligent users find ways to use your access app in a sensible way and integrate it with word, outlook and excel etc in ways that you might not have even realised were possible.

I always caution users not to open and save excel files, csv's and so on, as opening them with excel and then saving them can very easily change the format and appearance of data, so it then might not work correctly in access.
 

RogerCooper

Registered User.
Local time
Today, 14:23
Joined
Jul 30, 2014
Messages
277
I have found corruption in Access databases to be a problem even when there is only a single user logged in. The only solution is to move the backend data outside of Access.
 

Users who are viewing this thread

Top Bottom