Tim Churches wrote:
>However, there was a big stink a few years ago when someone claimed they
>had discovered a fundamental flaw in the Jet database engine, which is
>what MS Access uses for its storage. It turned out that the "problem"
>was caused by the programmers in question treating the Jet engine as if
>it were a much simpler dBase-style ISAM data store and thus assuming
>that records would always be in the same order as they were when they
>were added. In fact, the Jet engine is much more sophisticated than that
>and it constantly re-organises records internally - it only guarantees
>that results obtained via SQL statements will be correct - if you bypass
>SQL you might be surprised.
>
The problem is that Access messes up references ("foreign keys"). I
wrote a quite extensive system for our surgical department 5 years ago.
It kept track of all procedures performed, associated codes (ICD,
procedural codes, outcome codes, procedure log for surgeons in training,
complications registry etc.) To my dismay, once we had a sizeable
database, it did not seem to count my procedures correctly. I had
estimated that I already had done all hernias required for my traiing,
but the database suggested far less.
After extensive debugging and creating large regression test databases
we found out that you can quite predictably mess up references simply by
crashing the system while entering data. Apparently, Access is/was not
capable of rolling back transactions correctly and allocated foreign
keys fairly randomly after a crash without complaining; even more
surprising was our finding that even "old" record sets that were NOT
used at the time of the crash could be corrupted that way.
Following our own testing, trawling the web for similar experiences
yielded enough links to make us stop using Access forever. Haven't
touched it since. We referred to Access as the "data lottery system".
Pity, as I was very fond of the user interface and the ease of producing
user interfaces to a database.
>Thus, I suspect that the problem related in this story, if true, is
>caused by the programmers doing a join between tables using direct
>programmatic access to the Jet tables and bypassing the SQL interface. I
>dimly recall a note in a Microsoft knowledgebase about this very issue.
>
All our joins were created "visually" using the tools provided by
Access. These visual tools produce SQL statements. Whether they were
used internally, I do not know.
Horst
