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

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
One word : Corruption.

Why Access databases are so sensitive and why corruption is so normal?
Actually everyone I know who works with Access, has already faced a corrupted database more than once or twice and I'm sure most of you have experienced it. If you haven't, it's a matter of time. Don't worry. You'll face it sooner or later.

I know as far as one sits in front of a PC, corruption may occur anytime. But with all the application I have used during past 25 years, none has been normal-to-be-corrupted like Access.
The word Corruption in Access communities is very popular and you can see it through a lot of posts, even here. While using the word "Corruption" in other forums is somehow prohibited.

As an engineer, I've been using a lot of more complex apps than Access for over 25 years and I've never seen a word about corruption in 2D Cad, 3D Cad, Visualize, Simulation, PDM or similar communities. Or it's been so rare that I don't even remember it.

We had a corrupted FE the other day. The FE was used to input data in SQL server database. It didn't contain any table. more than 40 forms, several reports and queries and modules. Replacing a backup solved the problem. But still it left a bad taste in me.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Jan 20, 2009
Messages
12,499
Your experience is nothing like mine. I found corruption to be vary rare and I've been using Access for seventeen years.

Do you have multiple users sharing the same front end file?
 
Last edited:

sonic8

AWF VIP
Local time
Today, 09:39
Joined
Oct 27, 2015
Messages
593
I've been using a lot of more complex apps than Access for over 25 years and I've never seen a word about corruption in 2D Cad, 3D Cad, Visualize, Simulation, PDM or similar communities.
OK, fine. Now get 20 or more users to open and edit one and the same file in one these "complex" apps at the same time. - After that, let's talk about corruption...
Oh, your "complex" apps don't support multiple users editing the same file at the same time? Well, maybe they are not so complex after all regarding file management.

But seriously, I hear what you are saying. Corruption with Access files is definitely a problem. And it is the reason why I don't use Access backend databases for anything else than temporary data. The problem of corruption is inherent with multi-user file access. The alternative is to use an active server DBMS as backend for your Access applications. With that I haven't a case of database corruption in 20+ years.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2013
Messages
14,390
complaints of corruption usually come from newbies who don't know or understand what they are doing - do it right and corruption is not a problem. There is also the perceived notion that Access is 'free' but will still be as robust as a paid for DBMS.

There are two primary reasons, sharing files as Galaxiom has alluded to and an interruption in the connection.

Often newbies will have come from a different background where literal file sharing does not happen (two users cannot have the the same excel file open at the same time for example, they each have the full file copy open on their own own machine - and one has to save as a different file). Web based apps do not literally share code - a user through a browser uploads a copy of the code and does not literally run the same code. Easy solution for access users, each user has their own copy of the front end.

Interruptions in connection can occur when users connect wirelessly to the BE whether it be from their device or somewhere along the path or perhaps due to a power outage at a critical moment. With modern equipment with higher reliability it is less of an issue but the access BE is prone to corruption in these cases. Agee with Sonic, use a server DBMS.

To me, the rules are simple

1. db must be split
2. each user has their own copy of the front end on their local device
3. the front end should be supplied as a .accde

which is pretty much the way a website is organised. And if you want to connect wirelessly to an Access BE, use terminal server or citrix

We had a corrupted FE the other day.
you haven't said whether the FE is shared or compiled
 

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
Do you have multiple users sharing the same front end file?
you haven't said whether the FE is shared or compiled
8 users have their own copy of the FE on their own PCs.
We also have 6 shared PCs. Other users login to the FE with their own ID and pass, do their job and log out with these shared PCs.
No one (can) use the same FE at the same time.
All connections are LAN. not even one WIFI. Though a wifi connection may cause problem for the BE not FE.
BE is SQL server.

Sometimes I connect to my PC with teamviewer or AnyDesk to check something.
 

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
complaints of corruption usually come from newbies
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2013
Messages
14,390
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.
It is not necessarily that many. it's all there in the advice provided by MS and mentioned many times in forums, they just don't spend the time understanding the product before using it. Only way to prevent it would be for access to be supplied as a FE development tool only, no JET/ACE BE supplied.

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?
it does to a certain extent - if a FE is opened by two users at the same time, neither can go into design view and make changes. Remember Access is a Rapid Application Development tool - the final product should be a .accde

Again in my case we don't have multi user on the same FE.
and is it .accde?
 

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
complaints of corruption usually come from newbies
No offence but This was one of the most interesting things I've ever heard.
If I buy a car without seat belts and crash into a wall and die, it doesn't matter I was a newbie or not. The responsibility is on the company that hadn't installed a seat belt. All drivers are not F1 drivers. That's what safety measures are for. To save newbies.

You may think I'm a broken record, but in my opinion If newbies are able to break Access, some safety measures should have been set.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2013
Messages
14,390
supply a .accde FE and it is very unlikely your will get corruption

but I really doesn't understand why several users are allowed to open the same file.
?? So you are saying several users can't open the same Excel file? They can, but only the first one can save changes back to the same file. With access, they can't even get to make changes. Don't confuse Access the FE development tool with ACE/JET database which comes free with it.

If I buy a car without seat belts and crash into a wall and die, it doesn't matter I was a newbie or not. The responsibility is on the company that hadn't installed a seat belt.
what a facetious argument - the manufacturer supplies a car with seatbelts - is it their fault if the driver chooses to ignore them?
 

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
what a facetious argument - the manufacturer supplies a car with seatbelts - is it their fault if the driver chooses to ignore them?
You didn't get the point. I was explaining a case when the car hasn't seat belt in design. Not that I ignored it.

supply a .accde FE and it is very unlikely your will get corruption
The possibility may be less. but still I've seen many post about broken accde.
But I think you are right. I will change them to .accde on the first opportunity.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2013
Messages
14,390
You didn't get the point. I was explaining a case when the car hasn't seat belt in design. Not that I ignore it.
I did - cars come with seatbelts and with instructions how to use. Access comes with some protection and instructions how to use. It doesn't come with the equivalent of 'no seat belts'. I think your analogy should be that a car won't start unless the seatbelt has been used. But there are situations where you want to start the car but not use a seatbelt - perhaps to warm it up on a frosty morning, open the bonnet to check a funny noise, servicing etc.

You can't cover for every eventuality, but the developer can do more - they can have code that detects if a FE is already open and automatically close it for subsequent users for example.

I've seen many post about broken accde.
perhaps provide some links? I would guess usual reasons are file not in a trusted location, trying to run 32bit .accde in 64bit access, windows updates, BE moved/renamed, etc none of which are actually corruption

I just googled '.accde corrupted'. Not one of the links I looked at actually had a corrupted .accde, - the issues were with the environment
 

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
You may be correct because I don't read the posts in depth. The corruption may be with the environment.
It was one of those in my bookmarks. But I haven't read it in full. Just the title.


The answer of another person who had a similar problem.
http://social.msdn.microsoft.com/Fo...he-vba-project-contained-in-it-cannot-be-read
Answer marked:
It was the C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7\vba7.dll version the one that had the error were 7.0.15 and we compiled on 7.0.16 when we overwrote them it no longer gave an error.
 

KitaYama

Active member
Local time
Today, 16:39
Joined
Jan 6, 2022
Messages
442
Again I think your advice is correct. I'll change the FEs to accde as soon as possible.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 03:39
Joined
Apr 27, 2015
Messages
4,990
I think your analogy should be that a car won't start unless the seatbelt has been used. But there are situations where you want to start the car but not use a seatbelt - perhaps to warm it up on a frosty morning, open the bonnet to check a funny noise, servicing etc.
Brilliant. I was trying to say the exact thing but couldn't quite frame it.
 

Isaac

Lifelong Learner
Local time
Today, 00:39
Joined
Mar 14, 2017
Messages
6,523
I was thinking like Galaxiom. Although my skill level isn't quite as advanced as Galaxiom, I've also been involved in at least moderately serious Access development for well over a decade and I can count on one figure the times that I myself have definitely, uncontrovertibly known that the database must have been corrupted WITHOUT one of the below rules having been violated. Then there are a few others, but other people were involved in those situations (as co-designers or just co-messing-things-up)

AND - in almost all cases, I believe the cause is the same thing I have said several times on AWF already: I cannot believe how often I encounter people trying their hand at Access developers who still violate these simple rules:

  1. Do not attempt to edit code in Break mode
  2. Don't do things drastically out-of-order, like creating a form and adding tons of code to it and testing that code before even saving or naming the form - not sure about this one but when I do stuff like this (which make no sense), I've experienced corruption-like results
  3. Do not run a database without the VBA project being Debug > Compile > Save 'd
  4. Once in break mode, follow common sense guidelines - get the code to a real 'Stop' or 'End' status before closing the database, Saving, or any other strange combination of things that I've seen people do
  5. Do not try to compact & repair over a network - especially one on VPN and Wifi
  6. Do not interrupt a database in any way while compacting
  7. Don't go crazy with Pause+Break - use it sometimes when needed, but don't just be hitting it every few seconds for various reasons over and over, a couple times when I've done that I feel it blows Access's mind a little. pause+break is treated as a break-glass tool, NOT a normal development tool.
Access is like any other tool. You can break it if you try, or if you refuse to learn the rules of engagement.

You may be right in the sense that some other platforms do better at simply refusing to 'do' the mis-guided, ill-advised, wrong things that developers try to do with them. I am not necessarily sure if semantically that is 'better', or just a bigger diaper.

Either way, I've had virtually no problems with corruption - IF I follow the rules that are commonly posted.
 

oleronesoftwares

Passionate Learner
Local time
Today, 00:39
Joined
Sep 22, 2014
Messages
1,138
My take on this is that no development platform is 100 percent perfect (Ms access is no exception), software developers using a platform must take time to master it and know how best to use it to achieve the users' needs.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:39
Joined
Feb 28, 2001
Messages
22,812
If multi user on the same FE is the problem why Access doesn't behave like Excel?

Has to do with rules on sharing files. Excel does not allow two users to open a workbook for writing at the same time. Any number of users can simultaneously open a workbook that is Read-Only. (Well, at least many users can do so at once.) Access, on the other hand, allows sharing of the BE file as long as the multiple users work in different areas of the file.

The problem isn't inherent to Access - it is inherent to databases. However, Access has to use a different protocol than other databases. Access uses the Microsoft File & Printer Sharing protocol named Server Message Block (SMB), which allows sharing of PARTS of files. Access was built to be a small-business tool, which it is. It just got out of hand as people "pushed it" harder than it was designed to handle.

Other systems have the same problem that Access does - they just use a different protocol, one that wasn't necessarily around when Access v 1.0 was first released. I had a lot of users on my U.S. Navy database that was Access-based. I had a native FE AND a native BE file and they did fine. The only time I had issues was when some inexperienced person exhibited bad file-sharing behavior by opening a shared file and then leaving it open on his workstation - or worse, logged out and powered off the machine with the app still open. Folks who did that would get a very special visit from me AND from the department head who was technically the owner of the DB.

The problem that causes corruption is that you are actually working on part of the BE file when the app is open. If you make changes to the BE file but something causes them to be incomplete, you have a database that is time-inconsistent. The exact, bit-by-bit cause of corruption might be impossible to know, but the macroscopic cause is "incomplete updates." Think of it as taking a snapshot with a slow shutter speed and the images get blurred because of movement by the subject.

For what it is worth, ORACLE has the same problem if you are taking a backup and forget to put the DB in "backup" mode. In that particular case of negligence, the ORACLE DB is being copied while tables are being updated and if the update is done in a different order than the backup, you again have time-inconsistent data. There, instead of the Access "inconsistent database format" error, you get "inconsistent instantiation number" from ORACLE. Different message, same exact error - a blurred snapshot.
 

Cotswold

Active member
Local time
Today, 08:39
Joined
Dec 31, 2020
Messages
150
@TheDocMan
The only time I had issues was when some inexperienced person exhibited bad file-sharing behaviour by opening a shared file and then leaving it open on his workstation - or worse, logged out and powered off the machine with the app still open.
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.
 

Users who are viewing this thread

Top Bottom