- what is the reason that ms-access DBs sometimes has corruption issues?
- if a form is not opening and it is a corruption issue, would i solve the problem by eliminating the form?
views:
93answers:
2
+1
A:
Access can handle multi-user scenarios to some degree, but that's definitely not one of its strengths -- even though it's designed to support it, to me the support for multiple users feels "bolted on", and i see plenty of cases where multiple users accessing the same database could corrupt it (or at least, leave the data inconsistent). This is an inherent problem with nearly all client-side DBs, not just Access, but for some reason i hear about far more issues with Access than with other DBs. For single users, i don't think i've ever seen this problem, but i guess it's possible if Access gets killed (or crashes) while it's in the middle of an update, or possibly if you manage to copy the database while Access is still open (which could conceivably get you a corrupted copy).
Probably not. Unless it's the form itself that's corrupted, which seems rather unlikely.
cHao
2010-07-16 15:49:31
Multi-user is not "bolted on" -- it's wired into the bowels of Jet/ACE from the ground up. The reason Jet/ACE bogs down in multi-user scenarios is because multiple users are contending for the same file. This is inherent to any file-based database engine, and to be expected.
David-W-Fenton
2010-07-16 19:02:40
It feels "bolted on", whether it is or not. Access is horrible in multi-user scenarios, and that should have been obvious before the engine was even designed (since, as you've said, file-based DB engines are inherently bad at it). So designing it for multi-user use was either an afterthought or a really bad design choice.
cHao
2010-07-17 00:13:58
"Feels" is a subjective statement. It was not an afterthought in the design, nor a bad design choice, as it's done with the same basic approach to the problem that is used by every other multi-user file-based database engine, i.e., using some kind of locking file. On the facts, you are simply wrong, and on the subjective aspects of it, you are, if not alone, not convincing to anyone who has taken the time to understand the workings of the Jet/ACE database engines.
David-W-Fenton
2010-07-17 19:20:54
@David-W-Fenton: Funny...the only thing you're arguing with me about are the subjective statements i've made. Show me where i've said anything patently wrong.
cHao
2010-07-17 19:36:54
Quite frankly, i don't care about the people who've dived into the bowels of Access's DB engine. They already know more than most people ever will about it, maybe even how to not make it so horribly slow when used by more than one process at a time. For the rest of us, though, it suffices to say that Access sucks at multi-user.
cHao
2010-07-17 19:50:48
I guess you're proud of displaying your ignorance. I hope you never ever use Jet/ACE, because you're bound to frack it up because of your commitment to willful ignorance. Understanding how Jet/ACE handles multiple users does not require "diving into the bowels" of Jet/ACE. Indeed, it requires only a relatively minor amount of investigation and thinking to understand it. Anyone who is unwilling to do that shouldn't use it.
David-W-Fenton
2010-07-18 19:15:26
Comment flagged. Argue with me about the ups or downs of anything you like, prove me wrong if you can. But if you insist on making it personal and insulting me personally, GTFO.
cHao
2010-07-18 19:58:29
As for the premise that started all this...yes, i'm aware there's user access controls, page locking, and all that. However, i'm also aware that it's all but impossible to achieve decent performance and foolproof sharing/locking at the exact same time (this isn't even an Access/Jet thing; it's a network/latency/concurrency thing). This makes nearly *all* client-side DBs a bad choice for anything massively multi-user. (One or two users might be fine. Anything over about 10, forget it.) Access isn't special; it just happens to be what we're discussing at the moment.
cHao
2010-07-18 22:05:18
Your last comment is very different from your initial assertion, and had that been what you'd written, I would have quibbled with the 10 users but not with anything else.
David-W-Fenton
2010-07-19 20:00:38
You made it personal by writing "feels 'bolted on'". That's not a factual assertion, but a subjective comment, one that has no real basis in anything factual, as it's quite clear from the history of the Jet database engine and its basic architecture that multi-user access was part of the engine from the beginning. So, however it "feels" to you, the reality is that your feeling is not factually correct, and I would say is thus misleading and worthy of being called out.
David-W-Fenton
2010-07-19 20:02:11
My feeling, sure. If you can call it out, sure. But I wasn't insulting you -- i was talking Access and Jet, if in a less than flattering way. There's quite a difference between commenting about something (even if unflattering, even if subjective...even if wrong!) and commenting about some*one*. Argue with the facts, or the feelings if you feel the need, but insulting people is counter-productive, and causes me (probably among others) to mentally discount anything you said -- even if it happened to be totally true and blow someone's claims out of the water.
cHao
2010-07-19 20:42:13
And my "last comment" is not very different -- it was the *basis* for my initial assertion. I don't pick on Access because it's Access, or Jet because it's Jet -- i pick on it because its very purpose (providing a file-based DB) makes it inherently bad at certain things (like mass sharing), and it's a bad idea to use it for those things -- and by extension, a bad idea to add the capability for those things knowing they're a bad idea.
cHao
2010-07-19 20:50:31
For the record, i rather like Access. For small, single-user stuff or possibly a front end to the real database. I just refuse to use it for shared data. That's what SQL Server is for.
cHao
2010-07-19 21:05:13
@cHao: David-W-Fenton is correct in saying, "[multi-user is] wired into the bowels of Jet/ACE from the ground up" but **you** state a widely-held opinion when you say, "that's definitely not one of its strengths": consider that the reason Jet/ACE's bowels are on the ground is that guys like us have kicked seven shades of **** out of it ;)
onedaywhen
2010-07-20 08:03:22
@onedaywhen: I don't even hate Jet/Access. I like it for what it is: a decent single-access client-side database engine and designer. I just hate its being applied to scenarios where it's inherently going to have problems, and the fact that it's designed for the very scenarios where it shouldn't be used. I'd call that a flaw, even if Jet fans wouldn't.
cHao
2010-07-20 08:22:34
"I like it for what it is: a decent single-access client-side database engine and designer." - Interesting. That may be what *you* use it for. However, the original design goal was, "How to share data in a file", so "multi-user" and "file-based" are fundamental design features. For the record, what I hate about Jet/ACE is that its original SQL syntax was not good enough, when the SQL Server team tried to beef up the syntax they could get it good enough and now the competition is so far ahead I'm sure it can never be good enough. And I hate having to agree with David-W-Fenton about multiuser ;)
onedaywhen
2010-07-20 10:16:15
The original design goal was a case of solving the wrong problem. There are far better ways to share data, especially over a network, than any purely client-side system can provide. Especially these days, when stuff like SQL Server and MySQL are available for free.
cHao
2010-07-20 10:26:36
This last comment is what you should have said on the front end. I would disagree with it (there is no one-size-fits-all solution to all database problems, and different db engines will be good for different environments), but not on any factual basis.
David-W-Fenton
2010-07-20 17:17:49
@cHao: I didn't insult you. I pointed out that your comment implied something that was false. That's criticism of your WORDS not of you personally. If you're so attached to your words that you can't separate those two, then perhaps SO is not a proper forum for you to be participating in.
David-W-Fenton
2010-07-20 17:19:23
@David-W-Fenton: "I guess you're proud of displaying your ignorance. I hope you never ever use Jet/ACE, because you're bound to frack it up because of your commitment to willful ignorance." That's not criticizing my words; that's criticizing *me*. That's you making a judgement about the person *behind* the words -- and if that wasn't how it was meant, then you seriously need to think about what your words actually mean before you post them.
cHao
2010-07-20 18:22:35
\/\/hatever....
David-W-Fenton
2010-07-21 17:58:04
+1
A:
Someone emailed me this very question yesterday, and I answered them thus:
Have a look at my answers on SO that touch on the issue of corruption as well as these valuable articles by Tony Toews:
There are a number of types of corruption and avoiding different types requires different solutions. I cover most of those in my SO answers, while other issues are touched on in Tony's articles.
Specifically, memo pointer corruption is one type, while VBA project corruption is another. Most people aren't referring to either of those when they mention Access/Jet/ACE corruption, though.
David-W-Fenton
2010-07-16 19:05:26